Stored Procedure With Real Time Scenario In SQL Server

In this blog, you will learn, how to create a stored procedure with a real time scenario, using 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:

1. Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,
2. 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.
3. 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.