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.