557 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 two 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. |
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.
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.
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.
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.
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!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
From: | Chris-NC |
When: | 12 Apr 20 at 11:54 |
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.
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.