562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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. |
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.
To learn more about passing parameters to stored procedures have a look at our SQL training page, or book a place on our advanced SQL course!
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
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.