Click here to Skip to main content
11,721,053 members (78,754 online)
Click here to Skip to main content

Tagged as

SQL Server – How to write stored procedures with output parameters?

, 9 Jul 2014 CPOL 71.3K 5
Rate this:
Please Sign up or sign in to vote.
CodeProject In last day, 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

In last day, we were discussing about different kinds of Templated Helpers in ASP.Net MVC. You can read that article hereFor 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.

SQL1

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
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id)
FROM tblEmployee
WHERE Gender = @Gender
END

SQL2

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

SQL3

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’
ELSE
PRINT ‘@EmployeeTotal is not null’

SQL4

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

SQL5

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 is in action. If we want to find out more information about the stored procedure we have just created, we can use sp_help spGetEmployeeCountByGenderWhile executing this, we could see the name of the stored procedure, type, created date, parameters, their data types etc.

SQL6

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.

SQL7

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

For example, when we use sp_helptext spGetEmployeeCountByGender, we will get the text of this stored procedure.

SQL8

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

SQL10

In the above example, sp_depends tblEmployee 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.

Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)


License

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

Share

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 http://BestTEchnologyBlog.com. 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, 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    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.150901.1 | Last Updated 9 Jul 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid