Click here to Skip to main content
13,000,742 members (57,470 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


8 bookmarked
Posted 9 Jul 2014

SQL Server – How to Write Stored Procedures With Output Parameters?

Rate this:
Please Sign up or sign in to vote.
How to write stored procedures with output parameters in SQL Server

In the past few days, we were discussing about different kinds of Templated Helpers in ASP.Net MVC. You can read that article here. For a change, I am switching over to SQL Server. Today, we will discuss about writing stored procedures with output parameters in SQL Server.

Let’s understand this with an example. In the example, we will be using tblEmployee table.


To create a stored procedure with output parameter, we use the keywords OUT or OUTPUT. @EmployeeCount in the below stored procedure is an OUTPUT parameter. Notice that it is specified with OUTPUT keyword.

CREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
SELECT @EmployeeCount = COUNT(Id) 
FROM tblEmployee 
WHERE Gender = @Gender


To execute this stored procedure with OUTPUT parameter, follow the below steps:

  1. First, initialise a variable of same datatype as that of the output parameter. Here, we have declared @EmployeeTotal integer variable.
  2. Then pass the @EmployeeTotal variable to the stored procedure. You have to specify the OUTPUT keyword. If you don’t specify the OUTPUT keyword, the variable will be NULL.
  3. Then execute the stored procedure.
DECLARE @EmployeeTotal int
EXECUTE spGetEmployeeCountByGender ‘Female’, @EmployeeTotal output
PRINT @EmployeeTotal


If you don’t specify the OUTPUT keyword, while executing the stored procedure, the @EmployeeTotal variable will be NULL. In the example below, we have not specified OUTPUT keyword. So while executing the stored procedure, a message of @EmployeeTotal is null is printed.

DECLARE @EmployeeTotal int
EXECUTE spGetEmployeeCountByGender ‘Female’, @EmployeeTotal

IF(@EmployeeTotal is null)
PRINT ‘@EmployeeTotal is null’
PRINT ‘@EmployeeTotal is not null’


While using the parameter names, you can pass the parameters in any order. In the example below, we are first passing the OUTPUT parameter and then the input @Gender parameter. But we will get the total number of male employees without any errors.

DECLARE @EmployeeTotal int
EXECUTE spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = ‘Male’
PRINT @EmployeeTotal


Now let’s have a quick look at some of the extremely useful system stored procedures.

  • sp_help SP_Name: Used to view the information about the stored procedure like parameter names, their datatypes, etc. sp_help can be used with any database object, like Tables, Views, SPs, Triggers, etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.

Let’s see this in action. If we want to find out more information about the stored procedure we have just created, we can use sp_help spGetEmployeeCountByGender. While executing this, we could see the name of the stored procedure, type, created date, parameters, their data types, etc.


You can use sp_help with any database objects like Tables, Views, Triggers, etc. For example, when we use sp_help with tblEmployee table, we will get all the information about the table like different columns present in the table, their data types, indexes associated with the table, constraints associated with the table, etc.


  • sp_helptext SP_Name: Used to view the Text of the stored procedure.

For example, when we use <span style="color:#800000;">sp_helptext </span>spGetEmployeeCountByGender, we will get the text of this stored procedure.


  • sp_depends SP_Name: Used to view the dependencies of the stored procedure. This system stored procedure is very useful, especially if you want to check whether there are any stored procedures that are referencing a table which you are about to drop. sp_depends can also be used with other database objects like Tables, Views, etc.


In the above example, <span style="color:#800000;">sp_depends tblEmployee</span> statement gives a result that there is a stored procedure which is dependent on tblEmployee table. So you have to be extremely careful while dropping this table.



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


About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

You may also be interested in...

Comments and Discussions

QuestionThanks and it's working fine. Pin
GovindharajK9-Apr-17 7:22
memberGovindharajK9-Apr-17 7:22 
QuestionThanks, but... Pin
Carlo Alfio Flores29-Jul-15 3:58
memberCarlo Alfio Flores29-Jul-15 3:58 
GeneralMy vote of 5 Pin
CatchExAs9-Jul-14 3:48
professionalCatchExAs9-Jul-14 3:48 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170624.1 | Last Updated 9 Jul 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid