Please enable Javascript to correctly display the contents on Dot Net Tricks!

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

Posted By : Shailendra Chauhan, 21 Apr 2011
Updated On : 24 Jun 2014
Total Views : 165,967   
Support : SQL Server 2005,2008,2012
 

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 stored procedure. We can put multiple sql statements with in a single stored procedure. Below code is used to fetch data from a table "Employee" using 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 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 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

  1. In stored procedure we use output parameter to return multiple values.

  2. Generally we use output parameter in 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 stored procedure. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

 
Recommended for you
 
About the Author
Shailendra Chauhan

Shailendra Chauhan is an Entrepreneur, Author, Architect, and Corporate Trainer. He has rewarded as Microsoft MVP for his exceptional contributions in Microsoft Visual Studio and Development Technologies.

With more than 7 years in hand experience Shailendra Chauhan is a polymath in the domains of Microsoft .NET technologies and an array of other technologies including JavaScript, AngularJS, Node.js, Ionic and NoSQL Databases to name but a few.

He is the author of some of most popular e-books which encompass technical Interview on Node.js Interview Questions and Answers , ASP.NET MVC Interview Questions and Answers , AngularJS Interview Questions and Answers and LINQ Interview Questions and Answers. Furthermore he is a technical reviewer for book on ASP.NET MVC 4 Mobile App Development. Know more...
 
Free Interview Books
 
10 SEP
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN 05:00 PM-06:30 PM IST

More Details
7 SEP
ASP.NET MVC with AngularJS Development (online)

Weekdays (Mon-Fri) 07:30 AM-09:00 AM IST

More Details
29 AUG
NodeJS Development (online)

MON-FRI 06:00 AM-07:30 AM IST

More Details
27 AUG
ASP.NET MVC with AngularJS Development (online)

Weekend (Sat, Sun) 03:00 PM-05:00 PM IST

More Details
27 AUG
PPC Marketing (offline)

Sat, Sun 03:00 PM-05:00 PM IST

More Details
20 AUG
NodeJS Development (offline)

Sat, Sun     11:00 AM-12:30 PM IST

16 AUG
NodeJS Development (online)

Mon-Fri     09:00 PM-10:30 PM IST

12 AUG
ASP.NET MVC with AngularJS Development (online)

Mon-Fri     09:30 PM-11:00 PM IST

5 AUG
AngularJS Development (online)

Mon-Fri     08:00 PM-09:30 PM IST

1 AUG
ASP.NET MVC with AngularJS Development (online)

Mon-Fri     07:30 AM-09:00 AM IST

24 JUL
AngularJS Development (offline)

Sat,Sun     08:00 AM-09:30 AM

24 JUL
ASP.NET MVC with AngularJS Development (offline)

Sat, Sun     09:30 AM-11:00 AM

14 JUL
PPC Marketing (offline)

Sat, Sun     12:00 AM-02:00 PM IST

11 MAY
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 
+