Click here to Skip to main content
11,502,821 members (55,905 online)
Click here to Skip to main content

Sql Server - How To Write a Stored Procedure in SQL Server

, 12 Nov 2010 CPOL 687.1K 63
Rate this:
Please Sign up or sign in to vote.
Stored Procedure in SQL Server can be defined as the set of logically group of SQL statement which are grouped to perform a specific task. This article will describe the Stored Procedure in SQL Server and their benefits.

Stored Procedure: Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which are grouped to perform a specific task. There are many benefits of using a stored procedure. The main benefit of using a stored procedure is that it increases the performance of the database.The other benefits of using the Stored Procedure are given below.

Benefits of Using the Stored Procedure

  1. One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
  2. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
  3. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time.
  4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
  5. Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.

How to Write a Stored Procedure in SQL Server

Suppose there is a table called tbl_Students whose structure is given below:

CREATE TABLE  tbl_Students

(
    [Studentid] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](200) NOT  NULL,
    [Lastname] [nvarchar](200)  NULL,
    [Email] [nvarchar](100)  NULL
)

Support we insert the following data into the above table:

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Vivek', 'Johari', 'vivek@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Pankaj', 'Kumar', 'pankaj@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Amit', 'Singh', 'amit@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Abhishek', 'Singh', 'abhishek@abc.com')

Now, while writing a Stored Procedure, the first step will be to write the Create Procedure statement as the first statement:

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
     Sql statement used in the stored procedure
End

Now, suppose we need to create a Stored Procedure which will return a student name whose studentid is given as the input parameter to the stored procedure. Then, the Stored Procedure will be:

/*  Getstudentname is the name of the stored procedure*/

Create  PROCEDURE Getstudentname(

@studentid INT                   --Input parameter ,  Studentid of the student 

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid 
END

We can also collect the student name in the output parameter of the Stored Procedure. For example:

 /* 
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                       --Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Note:-/* */ is used to write comments in one or multiple lines

-- is used to write a comment in a single line

How to Alter a Stored Procedure in a SQL Server

In SQL Server, a stored procedure can be modified with the help of the Alter keyword. Now if we want to get student email address through the same procedure GetstudentnameInOutputVariable. So we need to modify it by adding one more output parameter " @StudentEmail " which is shown below:

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the
email address of the student with the help of the Alert Keyword
*/ 

Alter  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                   --Input parameter ,  Studentid of the student
@studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname, 
    @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

Note: It is not necessary that a stored procedure will have to return. There can be a case when a stored procedure doesn't returns anything. For example, a stored procedure can be used to Insert, delete or update a SQL statement. For example, the below stored procedure is used to insert value into the table tbl_students.

/*
This Stored procedure is used to Insert value into the table tbl_students. 
*/

Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
) 
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End

Execution of the Stored Procedure in SQL Server

Execution of the Stored Procedure which doesn't have an Output Parameter

A stored procedure is used in the SQL Server with the help of the "Execute" or "Exec" Keyword. For example, if we want to execute the stored procedure "Getstudentname", then we will use the following statement.

Execute Getstudentname 1
Exec Getstudentname 1

Execution of the Stored Procedure using the Output Parameter

If we want to execute the Stored procedure "GetstudentnameInOutputVariable" , then we first need to declare the variable to collect the output values. For example:

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure

Summary

In the end, we can say that a Stored procedure not only enhances the possibility of reusing the code and execution plan, but it also increases the performance of the database by reducing the traffic of the network by reducing the amount of information sent over the network.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vivek Johari
Technical Lead
India India
I am currently working as a Analyst and have around 7.5 years of experience in database.

Degree:-
Master Degree in Computer(MCA)

Work experience:-
Designing of the database.
Database Optimization.
Writing Complex Stored Procedures,Functions,Triggers etc.
Designing and developing SSIS & DTS packages.
Designing SQL Reports using SSRS.
Database Server Maintenance.

Certification:-
Microsoft certified Sql DBA in Sql server 2008 (MCTS).
Microsoft certified BI professional in Sql server 2008 (MCTS).
Oracle certified profession DBA in ORACLE 10g (OCP)
certified profession DBA in ORACLE 9i (OCP)

My other publication
Technical Blog:- Technologies with Vivek Johari

Moderator and Blogger at BeyondRelational.com

Guest Author and Blogger at sqlservercentral.com
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralGood Suggestion Pin
Member 116647654-May-15 20:29
memberMember 116647654-May-15 20:29 
QuestionThank you Pin
Pr!y@20-Feb-15 8:24
memberPr!y@20-Feb-15 8:24 
QuestionGreat article but you should use "return" instead of "written" Pin
MadhureshK8-Oct-14 2:45
memberMadhureshK8-Oct-14 2:45 
AnswerRe: Great article but you should use "return" instead of "written" Pin
ManojSridhar9119-Mar-15 1:36
memberManojSridhar9119-Mar-15 1:36 
AnswerRe: Great article but you should use "return" instead of "written" Pin
Vivek Johari24-Mar-15 21:46
memberVivek Johari24-Mar-15 21:46 
SuggestionThis extremely simplistic useless article Pin
PokemonCraft24-Aug-14 13:55
memberPokemonCraft24-Aug-14 13:55 
GeneralRe: This extremely simplistic useless article Pin
Vivek Johari24-Mar-15 2:12
memberVivek Johari24-Mar-15 2:12 
GeneralMethods of Writing Appllication Pin
Member 109771611-Aug-14 4:27
memberMember 109771611-Aug-14 4:27 
GeneralRe: Methods of Writing Appllication Pin
Akhil Mittal 3-Aug-14 23:17
mvp Akhil Mittal 3-Aug-14 23:17 
QuestionUse of Output parameters Pin
Ajay Shedge21-Jul-14 2:15
professionalAjay Shedge21-Jul-14 2:15 
Questionoutput parameter with WITH Pin
Member 1094936515-Jul-14 18:48
memberMember 1094936515-Jul-14 18:48 
General-- Pin
Member 1090459810-Jul-14 0:54
memberMember 1090459810-Jul-14 0:54 
Questioncreating a summarized report in MS SQL Stored Procedure Pin
Member 109280446-Jul-14 13:29
memberMember 109280446-Jul-14 13:29 
GeneralTHANK YOU - BEST ARTICLE EVER Pin
Ankit Mishra28-Jun-14 0:41
memberAnkit Mishra28-Jun-14 0:41 
GeneralRe: THANK YOU - BEST ARTICLE EVER Pin
Vivek Johari1-Jul-14 20:19
memberVivek Johari1-Jul-14 20:19 
GeneralExcellent Article! Pin
rkrockr18-Jun-14 19:45
grouprkrockr18-Jun-14 19:45 
GeneralRe: Excellent Article! Pin
Vivek Johari1-Jul-14 20:19
memberVivek Johari1-Jul-14 20:19 
GeneralRe: Excellent Article! Pin
ravithejag16-Feb-15 21:57
memberravithejag16-Feb-15 21:57 
QuestionSum of No of Hours Pin
Priyant Jain29-Apr-14 22:30
memberPriyant Jain29-Apr-14 22:30 
QuestionDOUBT [modified] Pin
gnana11926-Mar-14 19:22
membergnana11926-Mar-14 19:22 
QuestionBest One !! Pin
Gaganbhatia2-Mar-14 5:45
memberGaganbhatia2-Mar-14 5:45 
AnswerRe: Best One !! Pin
Vivek Johari13-Mar-14 17:34
memberVivek Johari13-Mar-14 17:34 
QuestionRegarding SQL Pin
Member 1062802426-Feb-14 7:31
memberMember 1062802426-Feb-14 7:31 
AnswerRe: Regarding SQL Pin
Vivek Johari13-Mar-14 17:33
memberVivek Johari13-Mar-14 17:33 
GeneralNice Code Pin
Patil Kishor30-Nov-13 4:38
memberPatil Kishor30-Nov-13 4:38 
GeneralRe: Nice Code Pin
Vivek Johari30-Nov-13 21:21
memberVivek Johari30-Nov-13 21:21 
GeneralSimple and clear Pin
karthik cad/cam24-Oct-13 22:02
memberkarthik cad/cam24-Oct-13 22:02 
GeneralRe: Simple and clear Pin
Paulo Augusto Künzel30-Oct-13 2:31
professionalPaulo Augusto Künzel30-Oct-13 2:31 
GeneralRe: Simple and clear Pin
Vivek Johari30-Nov-13 21:20
memberVivek Johari30-Nov-13 21:20 
GeneralRe: Simple and clear Pin
Vivek Johari30-Nov-13 21:21
memberVivek Johari30-Nov-13 21:21 
GeneralMy vote of 4 Pin
T.Vamshi krishna Reddy28-Aug-13 19:42
professionalT.Vamshi krishna Reddy28-Aug-13 19:42 
GeneralRe: My vote of 4 Pin
Vivek Johari9-Sep-13 1:31
memberVivek Johari9-Sep-13 1:31 
Generalvery nice Pin
Aijaz44412-Aug-13 7:11
memberAijaz44412-Aug-13 7:11 
GeneralRe: very nice Pin
Vivek Johari9-Sep-13 1:35
memberVivek Johari9-Sep-13 1:35 
QuestionPrecise & Absolute:) Pin
Vick_Web21-Mar-13 20:19
memberVick_Web21-Mar-13 20:19 
AnswerRe: Precise & Absolute:) Pin
Vivek Johari9-Sep-13 1:42
memberVivek Johari9-Sep-13 1:42 
GeneralMy vote of 5 Pin
Decoyer24-Jan-13 4:33
memberDecoyer24-Jan-13 4:33 
GeneralRe: My vote of 5 Pin
Vivek Johari9-Sep-13 1:43
memberVivek Johari9-Sep-13 1:43 
Questiontks Pin
Bobby43628-Dec-12 6:29
memberBobby43628-Dec-12 6:29 
AnswerRe: tks Pin
Vivek Johari9-Sep-13 1:42
memberVivek Johari9-Sep-13 1:42 
QuestionMy vote of 5 Pin
chandu G29-Nov-12 18:48
memberchandu G29-Nov-12 18:48 
AnswerRe: My vote of 5 Pin
Vivek Johari9-Sep-13 1:43
memberVivek Johari9-Sep-13 1:43 
GeneralMy vote of 5 Pin
chandu G29-Nov-12 18:47
memberchandu G29-Nov-12 18:47 
GeneralRe: My vote of 5 Pin
Vivek Johari9-Sep-13 1:43
memberVivek Johari9-Sep-13 1:43 
GeneralMy vote of 3 Pin
Arpan Guin22-Oct-12 4:28
memberArpan Guin22-Oct-12 4:28 
GeneralRe: My vote of 3 Pin
Vivek Johari9-Sep-13 1:35
memberVivek Johari9-Sep-13 1:35 
GeneralMy vote of 5 Pin
gramesh.hi8-Oct-12 3:37
membergramesh.hi8-Oct-12 3:37 
GeneralRe: My vote of 5 Pin
Vivek Johari9-Sep-13 1:31
memberVivek Johari9-Sep-13 1:31 
QuestionHow to Fetch the data from multiple tables using Linq in MVP Pin
Neena Panicker23-Aug-12 0:24
memberNeena Panicker23-Aug-12 0:24 
QuestionJoins and stored procedures Pin
Biodude Basava20-Jul-12 21:54
memberBiodude Basava20-Jul-12 21:54 
I have these Data:-

----------------------- 1 ---------------------------------------------------------

select Com.CountryName+', '+Cm.CityName+', '+Fac.FacilityName+', '+FM.FloorCode as Details

from MC_MeetingRoom_Master MR join MC_Floor_Master FM on MR.FloorID=FM.FloorID AND FM.IsActive=1

JOIN MC_Building_Master BM on BM.BuildingID=FM.BuildingID AND BM.IsActive=1

JOIN MC_Facility_Master Fac on BM.FacilityID=Fac.FacilityID and Fac.IsActive=1

join MC_City_Master Cm on Fac.CityID = Cm.CityID and Cm.IsActive=1

join MC_Country_Master Com on Cm.CountryID=Com.CountryID and Com.IsActive=1

WHERE MeetingRoomID=1000


The above query shows:-

India, Chennai, CHN - DLF Infocity - SEZ, 3rd Floor

-------------------------- 2 -------------------------------------------------------------


Another table where

select [MeetingRoomID],[Capacity],[MeetingRoomVNET], CASE WHEN [HasProjector] =0 then 'No' ELSE 'Yes' END [HasProjector],CASE WHEN [HasLan]=0 then 'No' ELSE 'Yes' END [HasLan],CASE WHEN [HasComputer]=0 then 'No' ELSE 'Yes' END [HasComputer],CASE WHEN [HasWhiteBoard]=0 then 'No' ELSE 'Yes' END [HasWhiteBoard],[MeetingRoomAdminMail]

from MC_MeetingRoom_Master where [MeetingRoomID]=1023

The above query shows columns:-

1023 25 0 No No No No Admin.Helpdesk@cognizant.com

-------------------------------------------------------------------------------------------

But i wanted to join both the queries...??

Do i need to create stored procedures..??

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 12 Nov 2010
Article Copyright 2010 by Vivek Johari
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid