Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Output Parameters

By Robert Marda, (first published: 2003/11/25)

Introduction

Output parameters allow you to retrieve values from a stored procedure after the stored procedure finishes executing. These values can be set and/or manipulated by the stored procedure. Output parameters can be any data type. This makes them more useful than the return value which only returns an int data type. You can have multiple output parameters.

Defining Output Parameters

For all the examples in this article I will use the following stored procedure:
CREATE PROCEDURE spOutput
@intValue int = 0 OUTPUT,
@strValue varchar(30) = 'Default varchar' OUTPUT,
@bitValue bit = 0 OUTPUT
AS

SET NOCOUNT ON

SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]

SET @intValue = @intValue + 10
SET @bitValue = CASE @bitValue WHEN 0 THEN 1 ELSE 0 END
SET @strValue = 'OLD_' + @strValue

SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]
I have made all three parameters for this stored procedure output parameters.

Using Output Parameters

Example 1 You can execute a stored procedure with output parameters without capturing the values. The SP simply executes as usual. You can execute spOutput without specifying any parameters. When you do you will get the following results:
@intValue   @strValue                      @bitValue 
----------- ------------------------------ --------- 
0           Default varchar                0

@intValue   @strValue                      @bitValue 
----------- ------------------------------ --------- 
10          OLD_Default varchar            1
Example 2 Should you decide you need to capture the value of one of the output parameters you can declare a variable and use the OUTPUT keyword as shown below:
DECLARE @intValue1 int

SET @intValue1 = -5

EXEC spOutput @intValue = @intValue1 OUTPUT, @strValue = 'My test.', @bitValue = 1

PRINT @intValue1
After executing the above code you will see these results: @intValue @strValue @bitValue ----------- ------------------------------ --------- -5 My test. 1 @intValue @strValue @bitValue ----------- ------------------------------ --------- 5 OLD_My test. 0 5

You can only retrieve a value from an output parameter when you use a variable to send a value in.  Trying to place the keyword OUTPUT after a constant will reward you with the following error:

Server: Msg 179, Level 15, State 1, Line 5
Cannot use the OUTPUT option when passing a constant to a stored procedure.
So even though the stored procedure accepts the constant value you send in and makes changes to it you can’t get it into a variable unless you use a variable to send the value in. Example 3 You can capture all three values using the following code:
DECLARE @intValue1 int, @strValue varchar(20), @bitValue bit

SET @intValue1 = -5
SET @strValue = 'My test.'
SET @bitValue = 1

EXEC spOutput @intValue = @intValue1 OUTPUT, @strValue = @strValue OUTPUT, @bitValue = @bitValue OUTPUT

SELECT @intValue1, @strValue, @bitValue
In the above code you will notice that the last two parameters receive their values from a variable with the same name as the parameter. It really doesn’t matter to SQL Server if they have the same name or not. It depends on you if you need the names to be different or the same to keep them clear in your mind when reviewing your code. I usually keep them the same. Example 4 If you fail to use the OUTPUT keyword for one of the variables then you will not get the modified value back. The stored procedure will still use the value you send in, it just won’t update the variable without the OUTPUT keyword. Here is the code to execute:
DECLARE @intValue1 int, @strValue varchar(20), @bitValue bit

SET @intValue1 = -5
SET @strValue = 'My test.'
SET @bitValue = 1

EXEC spOutput @intValue = @intValue1, @strValue = @strValue OUTPUT, @bitValue = @bitValue OUTPUT

SELECT @intValue1, @strValue, @bitValue
After executing you will notice that the value of @intValue1 has not changed even though it was changed within the stored procedure. You will see another SQL Server error if you define a parameter as output when you execute a stored procedure but fail to declare the variable as output in the stored procedure. Here is the error you will get:
Server: Msg 8162, Level 16, State 2, Procedure spOutput, Line 0
Formal parameter '@intValue' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Conclusions

In this article I have shown you how to use output parameters in your stored procedures. Output parameters enable you to capture data in variables you can use once the stored procedure is finished executing. The keyword OUTPUT must be declared in the stored procedure and defined with the stored procedure execute command to make output parameters work.
Total article views: 31803 | Views in the last 30 days: 22
 
Related Articles
FORUM

Stored procedures with output parameters

Stored procedures with output parameters

FORUM

Return output parameter

output parameters

FORUM

ReturnValue vs. Output Parameter

ReturnValue vs. Output Parameter

FORUM

Stored procedures with output parameters

Stored procedures with output parameters

FORUM

maximum number of output parameters we can use in Stored procedure

maximum number of output parameters we can use in Stored procedure

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones