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'  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------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='Ramesh',@MName='Kumar',@Lname='Kashaudhan'

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