Output Parameters and Return Codes in SQL Server
Part three of a three-part series of blogs

This blog teaches you how to return values from your SQL stored procedures using two different techniques: return codes, and output parameters.

  1. Returning Values from Stored Procedures in SQL
  2. Using Return Codes with Stored Procedures
  3. Using Output Parameters (this blog)

This blog is part of a complete SQL tutorial. You can learn about the techniques used in this blog on our Advanced SQL training course.

Posted by Andrew Gould on 26 April 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using Output Parameters

Another technique for returning a value from a stored procedure is to use an output parameter. Each stored procedure can have many output parameters and each parameter can use any data type. Before discussing output parameters it might be worth reminding yourself how input parameters work.

Declaring Output Parameters

You declare output parameters in the definition of a procedure, in much the same way you declare input parameters. The only difference is that you add the word OUTPUT to the end. The procedure below has a single input parameter and a single output parameter:

CREATE PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

)

AS

BEGIN

SET @TotalBudget =

(

SELECT SUM(FilmBudgetDollars)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

)

END

Rather than using the RETURN statement, the value of an output parameter is set explicitly using the SET keyword. For the example above we could also just use the SELECT statement to set the value of the output parameter, as shown below:

CREATE PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

)

AS

BEGIN

SELECT @TotalBudget = SUM(FilmBudgetDollars)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

END

Capturing the Value of an Output Parameter

To capture an output parameter you need to use a variable. The example below uses a variable to store the result of the output parameter from our stored procedure:

DECLARE @Budget BIGINT

EXEC spYearBudget

1998

,@Budget OUTPUT

SELECT @Budget

When you call a stored procedure with both input and output parameters you provide values for the input parameters in the usual way. In the example above we've passed the value 1998 to the @FilmYear parameter. For each of the output parameters you specify the name of the variable you are using to hold the result followed by the word OUTPUT.

If you've decided to name the parameters when you call the stored procedure the syntax is slightly odd:

DECLARE @Budget BIGINT

EXEC spYearBudget

@FilmYear = 1998

,@TotalBudget = @Budget OUTPUT

SELECT @Budget

Normally when you assign a value to a variable the name of the variable appears to the left of the = sign. With an output parameter, however, the variable name appears to the right of the = sign.

The final line of code in our example simply displays the value of the output parameter, as shown in the diagram below:

Output parameter

The result of our output parameter.

 

Using Multiple Output Parameters

One of the advantages of output parameters over return codes is that a single stored procedure can have a number of output parameters. In the example below we've extended our stored procedure to return multiple values:

ALTER PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

,@AverageBudget DECIMAL(18,2) OUTPUT

,@HighestBudget BIGINT OUTPUT

,@LowestBudget BIGINT OUTPUT

,@NumberOfFilms INT OUTPUT

)

AS

BEGIN

SELECT

@TotalBudget = SUM(FilmBudgetDollars)

,@AverageBudget = AVG(CAST(FilmBudgetDollars AS DECIMAL))

,@HighestBudget = MAX(FilmBudgetDollars)

,@LowestBudget = MIN(FilmBudgetDollars)

,@NumberOfFilms = COUNT(FilmID)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

END

When we call the procedure we can now populate multiple variables and show a complete set of outputs instead of just a single value:

DECLARE @Total BIGINT

DECLARE @Average DECIMAL(18,2)

DECLARE @Highest BIGINT

DECLARE @Lowest BIGINT

DECLARE @Number INT

EXEC spYearBudget

@FilmYear = 1998

,@TotalBudget = @Total OUTPUT

,@AverageBudget = @Average OUTPUT

,@HighestBudget = @Highest OUTPUT

,@LowestBudget = @Lowest OUTPUT

,@NumberOfFilms = @Number OUTPUT

SELECT

@Total AS [Total Budget]

,@Average AS [Average Budget]

,@Highest AS [Highest Budget]

,@Lowest AS [Lowest Budget]

,@Number AS [Number of Films]

The result of calling this stored procedure is shown in the diagram below:

Multiple outputs

A neat set of outputs from a single stored procedure.

This blog has 0 threads Add post