Using Return Codes with Stored Procedures
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!
