Insert, Select, Update and Delete Records in a Single Stored Procedure Using SQL Server

Background

Sometimes there is a need to insert, select, update and delete records from a table using a single Stored Procedure instead of creating separate Stored Procedures for each operation.

Suppose I have one .aspx web page in which I need a to insert, select, update and delete records. To do that instead of creating four Stored Procedures to perform these tasks I will create a single Stored Procedure to satisfy my requirements and I will access it in code behind according to the action performed by the end user on a button click.

I have written this article specially focusing on newcomers and anyone new to SQL Stored Procedures so let us start with a basic introduction.

What is Stored Procedure?

A Stored Procedure is a group of logical SQL statements to perform a specific task such as insert, select, update and delete operations on a table and so on which is stored in a SQL database.

Creating a Stored Procedure

Before creating a Stored Procedure, we will create one table named employee in the SQL database which looks as in the following image.

I have set the primary key on the id column for the Identy specification.

Now we have a table to perform these operations. Now let us start to create the Stored Procedure.

The Stored Procedure is created using the keyword Create Procedure followed by the procedure name. Let us create the Stored Prcedure named EmpEntry as given below.

create Procedure EmpEntry
(
 --variable  declareations

@Action Varchar (10),                             --to perform operation according to string passed to this varible such as Insert,update,delete,select    
@id int=null,                                   --id to perform specific task
@Fname Varchar (50)=null,                     -- for FirstName
@MName Varchar (50)=null,                    -- for MName
@Lname Varchar (50)=null                      -- for LastName

)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---exec EmpEntry @Action='delete' ,@Fname='S',@MName='R',@Lname='M',@id='13'  --added by vithal wadje on 18-10-2012 for Csharp contribution
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
as
Begin
  SET NOCOUNT ON;

If @Action='Insert'   --used to insert records
Begin
Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End

else if @Action='Select'   --used to Select records
Begin
select *from employee
end

else if @Action='Update'  --used to update records
Begin
 update employee set FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
 End
 Else If @Action='delete'  --used to delete records
 Begin
 delete from employee where id=@id
 end
 End

In the above Stored Procedure throught comments I have clearly explained which block is used for which purpose, so I have briefly explained it again. I have used @Action variable and assigned the string to them and according to the parameter passed to the Stored Procedure the particular block will be executed because I have kept these blocks or conditions in nested If else if conditional statements.

 "The most important thing is that I have assigned null to each variable to avoid the effect on the parameter passed to the Stored Procedure because we are passing a different number of parameters but not the same number of parameters to the Stored Procedure to perform these tasks."

After creating this Stored Procedure, now let us use it.

To execute the Stored Procedure EmpEntry that we created we need to use the keyword exec followed by the procedure name and the parameter list. I have explained how to use it below.

Inserting the Records into the Employee table that we created with the EmpEntry procedure; see:

exec EmpEntry @Action='Insert' ,@Fname='vithal',@MName='G',@Lname='Wadje'

After running this query the records will be inserted into the table employee. To see the records inserted into the table the run following query:

select * from employee

the output will be as shown in the following:

Their are two records you have seen because I have executed the procedure two times.

Selecting Records From table

exec EmpEntry @Action='Select'


The output will be as follows:

Updating Records of table

exec EmpEntry @Action='Update' ,@Fname='Manish',@MName='Kapil',@Lname='Sawant',@id=2

After executing the above query the id number 2 record will be updated in the table.

To see, run the query: select * from employee

The output will be as shown in the following:

Deleting the Records from table

exec EmpEntry @Action='delete' ,@id=2

After executing the above query the id number 2 record will be deleted from the table.

To see, run the query: select * from employee

The output will be as shown in the following:

Note

In my next article I will explain how to use this Stored Procedure in an ASP.Net form in code behind on a button click. I hope this article is useful for all readers.