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.