Introduction to Stored Procedure in SQL Server with Examples

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 a 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.

Types Of Stored  Procedures

The following are the types of Stored Procedures:

• User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database
• System Stored Procedure: The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database
• Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server
• Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission
• Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

Advantages Of Stored Procedure

Stored Procedures have the following advantages:

1. Can reduce network traffic and latency, boosting application performance.
2. Execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
3. Help promote code reuse.
4. Can encapsulate logic. You can change stored procedure code without affecting clients.
5. Provides better security to your data.

Note

We can up to 2100 parameters in one Stored Procedure.

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 ed 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

The comments in the Stored Procedure above clearly explain 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 ed 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 ed to the Stored Procedure because we are ing a different number of parameters but not the same number of parameters to the Stored Procedure to perform these tasks." 

Using the Stored Procedure

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
  
  The following is a sample of inserting the records into the Employee table that we created with the EmpEntry procedure:
  
  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 run the 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:

Summary

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