Introduction to Stored Procedure in SQL Server with Examples

Vithal Wadje

12y86.2k31100Article

SQL ServerInsert RecordsDelete Records in single stored ProcedureSelect records

31 Comments

Join the conversation! Your thoughts help the community grow.

Sridhar Yelagandhula6y ago
Very useful article
- Vithal Wadje6y ago
  Glad to know it helped you..
santosh Bakale12y ago
Its Very Nice Article, but in select statement new record inserted with null values & gives result -- EXEC AddRetailer @QryType='Select', @isavailable=0, @isactive=1== isavailable is output parameter, isactive is in where condistion
- Vithal Wadje12y ago
  thanks,check your query
Vithal Wadje13y ago
thanks priya mdm
priya talole13y ago
Its very nice article also reduce code complexity thank u sir.
Vithal Wadje13y ago
thanks a lot Lajapathy sir
Vithal Wadje13y ago
thanks yogesh sir
Yogesh Bajpai13y ago
nice article.its reduce lot of code complexcity..
Lajapathy Arun13y agoedited
nice boss, Nice effort
Vithal Wadje13y ago
thanks
Mr Tue13y ago
thank you sir
Vithal Wadje13y ago
thank you Grabsi Amine sir
Vithal Wadje13y ago
thank you sudhakar sir
Grabsi Amine13y ago
Welcome , you're the best ^^
Vithal Wadje13y ago
thank you Grabsi Amine sir
Grabsi Amine13y ago
Amazing Work
Vithal Wadje14y ago
thank you Andrej Juhas
Vithal Wadje14y ago
thanks a lot Rajshri Madam,its my pleasure that my article helps you,for more code refer my all articles and blogs which are published
Andrej Juhas14y ago
Very inspiriting.
Rajshri Shitole14y ago
may be sir u write, but its lots benefit to me. from last 1 year i m doing difficult code for search , insert, delete and diffent 3 form making but its combine in 1 and time saving also. thanks if u hv any more code for basic coding plz give me.
Vithal Wadje14y ago
thank you Rajshri Madam, but their is no mistake in any syntax you need to read it carefully and your query in comment is fully wrong , i cant know where you see my mistake even every thing is ok. take your time to read carefully
Rajshri Shitole14y ago
good article but some mistak in insert query - put like this - Insert Into employee (id,FirstName,MName,LastName)values(@id,@Fname,@MName,@Lname) and for select query - do this- exec EmpEntry 'Select','','','',''
Rajshri Shitole14y ago
good article but some mistak in insert query - put like this - Insert Into employee (id,FirstName,MName,LastName)values(@id,@Fname,@MName,@Lname) and for select query - do this- exec EmpEntry 'Select','','','',''
Vithal Wadje14y ago
thanks reza
reza parmar14y ago
Thank you for submitting a complete education.
Vithal Wadje14y ago
dear mohan sir,if you have a knowldge about asp.net then you can eaisly implement on your own logic,its a very basic and small concept. If you want source code then wait for my next article
mohan kumar14y ago
Can i get the source code? I need C# design page (.aspx) and Code behind page to see how you have implemented this Stored Procedure.
Vithal Wadje14y agoedited
thanks Anshika Mittal and dipali goel, and its my pleasure that my articles helps all of you. and my intension in future is that to write the articles for students and freshers so they can not be got the difficulties on interview time. and remove the fear of interview from their mind
Anshika Mittal14y ago
Nice article Vithal your article is very helpful for all the fresher level students and others also...
dipali goel14y ago
wow thanx for sharing with us i'll try it definetly

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:

Can reduce network traffic and latency, boosting application performance.
Execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
Help promote code reuse.
Can encapsulate logic. You can change stored procedure code without affecting clients.
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.