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

CodeProject

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

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’
ELSE
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.

Reference

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