Stored Procedure With Real Time Scenario In SQL Server

In this blog, I am going to explain, how to create a procedure in the real time scenario.

What is a procedure:

A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input. Stored procedure will reduce the network traffic and increase the performance.

Real time scenario

Step 1: Create a table to describe and create the stored procedure.

create table Product
(
ProductId int primary key,
ProductName varchar(20) unique,
ProductQty int,
ProductPrice float
)

Step 2: Insert some value to the describe scenario.

insert product values(1,'Printer',10,4500)
insert product values(2,'Scanner',15,3500)
insert product values(3,'Mouse',45,500)

Step 3: Check your table with the inserted value.

select * from product

Step 4: Real time scenario is given below:

Create a stored procedure, which is used to perform the requirements, given below:

Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,
Before inserting, check the detail about the product name. If the product name is available, check the product price. If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
If first and second conditions are not satisfied, insert the product information, as new record into the table.

create procedure prcInsert
@id int,
@name varchar(40),
@qty int,
@price float
as
begin
declare @cnt int
declare @p float
select @cnt=COUNT(ProductId)from Product where pname=@name
if(@cnt>0)
begin
update Product set ProductQty=ProductQty+@qty where ProductName=@name
select @p=ProductPrice from Product where ProductName=@name
if(@p<@price)
begin
update Product set ProductPrice=@price where ProductName=@name
end
end
else
begin
insert Product values(@id,@name,@qty,@price)
end
end

That's it. Thank you. If any suggestion or clarification is required, kindly revert back with the comments.

5 Comments

Join the conversation! Your thoughts help the community grow.

Venu Gopal8y ago
Sorry i executed in a wrong way now its working thnakyouspproductdetailscheck @pid=2,@pname='scanner',@pqty=3,@pprice=1600
Venu Gopal8y ago
When i am trying to call the above storeprocedure its throwing an error prcinsert(2,'scanner',4,5600) the error is ----" Incorrect syntax near '2'.""
Kunal Panchal9y ago
Try to slove it bro
Kunal Panchal9y ago
This above stored procedure does not work it giving the error of Pname
- rahul brungi9y ago
  Yes i do get the same error when create procedure, instead of pname i have entered productname, then i didnt any errors and procedure had run successfully