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 (this blog)
- Using 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.
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 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.
You can also learn about stored procedures from our two-day advanced SQL course, or from the exercises, videos and blogs linked to from our SQL training home page.
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!
- Returning Values from Stored Procedures in SQL
- Using Return Codes with Stored Procedures (this blog)
- Using Output Parameters
Hi Andrew
I appreciate this is an old post - I came across it when looking into the message numbers as I want to avoid using system defined numbers.
Return messages for stored procedures are poorly documented as you say. The message for the internal error codes can be found by querying sys.messages, e.g.
select * from sys.messages where language_id = 1033 and message_id = 208
Which returns the message
Invalid object name '%.*ls'.
Not really much more helpful!
You can wrap the SP in a TRY CATCH:
DECLARE @ReturnCode INT
DECLARE @Error nvarchar(MAX);
BEGIN TRY
EXEC @ReturnCode = sp_executesql N'SELECT * FROM tblMovies'
SELECT @ReturnCode
END TRY
BEGIN CATCH
SET @Error =
N'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' +
'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' +
'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' +
'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' +
'Error Message: ' + ERROR_MESSAGE()
RAISERROR(@Error, 0, 1) WITH NOWAIT;
END CATCH
This will give some information and allow the error to be handled. However it won't work if you put the TRY CATCH inside the SP! The documentation for this is here.