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.

  1. Returning Values from Stored Procedures in SQL
  2. Using Return Codes with Stored Procedures (this blog)
  3. 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.

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:

Return code result

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

Return code fail

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

Result of calculated return code

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

Result of return code

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:

Success of system stored procedure

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

Return code fail

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!

Error codes

Not the most helpful message!

 
  1. Returning Values from Stored Procedures in SQL
  2. Using Return Codes with Stored Procedures (this blog)
  3. Using Output Parameters
This blog has 0 threads Add post