BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog teaches you how to return values from your SQL stored procedures using two different techniques: return codes, and output parameters.
- Returning Values from Stored Procedures in SQL
- Using Return Codes with Stored Procedures
- 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:
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:
A neat set of outputs from a single stored procedure.
- Returning Values from Stored Procedures in SQL
- Using Return Codes with Stored Procedures
- Using Output Parameters (this blog)