There are two techniques for returning values from a stored procedure: using return codes, and using output parameters. The main differences between these techniques are described in the table below:
Technique | Description |
|---|
Return code | A stored procedure can return a single value using a return code. The data type of the value must be an integer (a whole number). |
Output parameter | A stored procedure can have many output parameters. An output parameter can use any data type. |
Output parameters are more flexible than return codes but they are a little more fiddly to use. The rest of this tutorial will show you how to use each of these techniques, starting with return codes.
You can return a value from a stored procedure using the RETURN statement. Each stored procedure can only return one value each time it is called and the data type of the return code must be an integer.
Returning from a Stored Procedure
You can return from a stored procedure at any point to end the execution of the procedure. Normally you would combine the RETURN statement with an IF statement in order to return different values based on a condition, such as in the example shown below:
CREATE PROC spFindFilms
(
@FilmName VARCHAR(MAX)
)
AS
BEGIN
SELECT *
FROM tblFilm
WHERE FilmName LIKE '%' + @FilmName + '%'
IF @@ROWCOUNT > 0
RETURN 1
ELSE
RETURN 0
END
In the example above our stored procedure attempts to retrieve records from a table based on the value of a parameter. If the attempt successfully returns some records our stored procedure returns a value of 1. If the procedure doesn't find any records it returns a value of 0.
You can also use the RETURN statement by itself to immediately end the execution of a procedure without specifying a return value. When you do this the stored procedure will return 0.
Capturing a Return Code
In order to capture the value returned from a stored procedure you need a variable. The example below calls the stored procedure we have created, captures the return code in a variable and then displays the result:
DECLARE @ReturnCode INT
EXEC @ReturnCode = spFindFilms 'star'
SELECT @ReturnCode
The output of this code is shown in the diagram below:
When our call to the stored procedure returns some records our return code gives us a result of 1.
If we call the procedure and it doesn't return any records we'll see a different value returned by the procedure:
DECLARE @ReturnCode INT
EXEC @ReturnCode = spFindFilms 'twilight'
SELECT @ReturnCode
There's no sign of dreadful films in the database so our return code gives us a value of 0.
Returning Calculated Values
Rather than using an IF statement to return absolute values from a stored procedure you could instead return a calculated value. The example below simply returns the number of records selected by the query:
CREATE PROC spCountFilms
(
@FilmName VARCHAR(MAX)
)
AS
BEGIN
SELECT *
FROM tblFilm
WHERE FilmName LIKE '%' + @FilmName + '%'
RETURN @@ROWCOUNT
END
We can call this procedure to see the number of records returned:
DECLARE @ReturnCode INT
EXEC @ReturnCode = spCountFilms 'die'
SELECT @ReturnCode
The result of our calculated return code.
Aggregate functions, such as SUM, are good candidates for calculating the value of a return code, as in the example below:
CREATE PROC spTotalOscars
(
@FilmYear INT
)
AS
BEGIN
RETURN
(SELECT SUM(FilmOscarWins)
FROM tblFilm
WHERE YEAR(FilmReleaseDate) = @FilmYear)
END
This stored procedure returns the total number of Oscars that have been won by films released in the year specified by the parameter. The result of calling this procedure is shown below:
DECLARE @ReturnCode INT
EXEC @ReturnCode = spTotalOscars 1998
SELECT @ReturnCode
There were 16 Oscars won by films released in 1998 in our database.
Return Codes and System Stored Procedures
It's worth knowing that all of the system stored procedures in SQL Server have a return code. By default, if a system stored procedure returns a 0 it has succeeded; if it returns any value other than 0 it has failed in some way. The example below calls the system stored procedure called sp_executesql and captures the return code in a variable:
DECLARE @ReturnCode INT
EXEC @ReturnCode = sp_executesql N'SELECT * FROM tblFilm'
SELECT @ReturnCode
The output of this code is shown in the diagram below:
In this case the stored procedure worked and so returns a 0.
If we attempt to call the procedure and it fails, we'll see a different return code:
DECLARE @ReturnCode INT
EXEC @ReturnCode = sp_executesql N'SELECT * FROM tblMovies'
SELECT @ReturnCode
The stored procedure failed (because there is no table called tblMovies) and returned the value 208.
Microsoft's help page doesn't shed much light on exactly what the code 208 means, other than that it indicates a failure!
Not the most helpful message!
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.
