In this article, I am going to tell you how to create a procedure in the real time scenario. 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  

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.