CRUD Operation using Stored Procedure

Posted By : Shailendra Chauhan, 21 Apr 2011

Updated On : 18 Apr 2012

Tags : CRUD Operation using Stored Procedure, Stored procedure with example

In database, CRUD stands for 4 database operations: Create, Retrieve, 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.

Create Operation

We can create temporary table(s) using stored procedure for intermediate data manipulation. Below code is used to remove duplicate records from tables using procedure.

 CREATE TABLE Employee_Test
(
 Emp_ID int identity, Emp_Name varchar (55) NULL,
 Emp_Sal decimal (10, 2) NULL,
 Emp_Designation varchar (20) NULL
)
Go
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Riyaz',16000,'SE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Monu',27000,'SSE')
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE')
GO
--Now see table
SELECT * FROM dbo.Employee_Test

 create procedure usp_Removedup
as
begin
 CREATE TABLE #tempEmpdata
(
 Emp_Name varchar (55) NULL,
 Emp_Sal decimal (10, 2) NULL,
 Emp_Designation varchar (20) NULL
)
--Identify and save distinct data into temporary table
INSERT INTO #tempEmpdata SELECT DISTINCT Emp_Name,Emp_Sal, Emp_Designation FROM Employee_Test
--Delete original table data by using Truncate command, since it has Emp_ID as identiy col
TRUNCATE table Employee_Test
--Now insert data from temp table to original table
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation) SELECT Emp_Name,Emp_Sal,Emp_Designation FROM #tempEmpdata
--Now drop temp table
drop TABLE #tempEmpdata
end

 --Execute above created procedure to remove duplicate records 
exec usp_Removedup

 --now see original table
Select * from Employee_Test

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

In stored procedure we use output parameter to return multiple values.
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 CRUD operation in Sql Server using Stored Procedure. I hope after reading this article you will be know how to implement CRUD operation using stored procedure. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

« Prev