SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures

Print 5 min read

31 Aug 2022

Advanced

245K Views

In database, you need to do Insert, Update and Delete. If we want to make a reliable and high performance system then these four operations must be implemented by stored procedures. Stored procedure also prevents Sql Injection attacks and reduce network traffic. For more about stored procedure refer the article Stored Procedure Plan Recompilation and Performance Tuning.

Insert Operation

We can insert records into the table(s) using stored procedure by passing data in input parameters. Below code is used to insert record in the table "Employee" using stored procedure

 CREATE TABLE Employee
(
 EmpID int primary key, Name varchar(50),
 Salary int,
 Address varchar(100)
)

 Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')
--See table
SELECT * FROM Employee

 CREATE PROCEDURE usp_InsertEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int,
@Name varchar(50),
@Salary int,
@Address varchar(100)
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY 
Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address)
 set @flag=1;
IF @@TRANCOUNT > 0
 BEGIN commit TRANSACTION;
 END
 END TRY 
BEGIN CATCH
IF @@TRANCOUNT > 0
 BEGIN rollback TRANSACTION;
 END
 set @flag=0;
 END CATCH
END

 --Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,1,'Deepak',14000,'Noida'
if @flag=1
 print 'Successfully inserted'
else
 print 'There is some error'

 --Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,4,'Deepak',14000,'Noida'
if @flag=1
 print 'Successfully inserted'
else
 print 'There is some error'

 --now see modified table
Select * from Employee

Retrieve Operation

We can retrieve data from one or more tables/views with the help of join, using a stored procedure. We can put multiple SQL statements within a single stored procedure. Below code is used to fetch data from a table "Employee" using a stored procedure

 -- first we Insert data in the table
Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')
Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')
go
--Now we create a procedure to fetch data
CREATE PROCEDURE usp_SelectEmployee
As
Select * from Employee ORDER By EmpID

 --Execute above created procedure to fetch data
exec usp_SelectEmployee

Update Operation

We can update records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table "Employee" using a stored procedure

 CREATE PROCEDURE usp_UpdateEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int,
@Salary int,
@Address varchar(100)
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY
 Update Employee set Salary=@Salary, Address=@Address
 Where EmpID=@EmpID 
 set @flag=1; 
IF @@TRANCOUNT > 0
 BEGIN commit TRANSACTION;
 END
 END TRY
 BEGIN CATCH
IF @@TRANCOUNT > 0
 BEGIN rollback TRANSACTION; 
 END
 set @flag=0;
 END CATCH
 END

 --Execute above created procedure to update table
Declare @flag bit
EXEC usp_UpdateEmployee @flag output,1,22000,'Noida'
if @flag=1 print 'Successfully updated'
else
 print 'There is some error'

 --now see updated table
Select * from Employee

Delete Operation

We can delete records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table "Employee" using a stored procedure

 CREATE PROCEDURE usp_DeleteEmployee
@flag bit output,-- return 0 for fail,1 for success
@EmpID int
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY
 Delete from Employee Where EmpID=@EmpID set @flag=1; 
IF @@TRANCOUNT > 0
 BEGIN commit TRANSACTION;
 END
 END TRY
 BEGIN CATCH
IF @@TRANCOUNT > 0
 BEGIN rollback TRANSACTION; 
 END
set @flag=0; 
END CATCH 
END

 --Execute above created procedure to delete rows from table
Declare @flag bit
EXEC usp_DeleteEmployee @flag output, 4
if @flag=1
 print 'Successfully deleted'
else
 print 'There is some error'

 --now see modified table
Select * from Employee

Note

In a stored procedure we use an output parameter to return multiple values.
Generally, we use output parameter in a stored procedure to get status of the operation as I used above "@flag" output parameter to get operations status whether these are successfully executed or not.

Summary

In this article I try to explain basic Insert, Retrieve, Update, Delete Operations using Stored Procedures. I hope after reading this article you will be know how to implement these operations using a stored procedure. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.