• SQL Prompt
• SQL Code Analysis

Neglecting to Use, or Misusing, the RETURN Keyword (BP016)

All stored procedures, statement blocks, and batches return a code that records the success of its execution. If a batch or procedure reaches the end, it automatically returns 0 (zero), meaning success, unless you specify otherwise using the RETURN keyword, with an integer parameter. There is nothing defined for any number other than zero, but the convention exists that any other number signifies failure of some description. If an error occurs, you should trap the value that is returned and send it to the calling process, so that it can respond accordingly.

Only use a RETURN value to communicate the success or failure of the process, never to return values as part of a process, such as the number of purchases on a given day. Also, a stored procedure or batch should never have a RETURN keyword without a value, and SQL Prompt will issue a BP016 warning you if it detects this mistake.

What is the RETURN keyword, and what does it return?

To return a value from any stored procedure or batch that reports any problems, you need to assign it to a variable and use the RETURN control-of-flow keyword, with the value as a parameter. This RETURN will terminate the execution of the batch immediately and return the value you pass as a parameter. If you try to return NULL from a stored procedure using the RETURN keyword, you will get a warning, and 0 is returned instead. If a procedure hits an error that requires it to terminate immediately, it will return NULL because it never gets to either the RETURN keyword or the end of the batch! If a batch or procedure reaches the end, it automatically returns a zero.

Some system stored procedures return the actual error codes that occur when running the batch, including those specified in RAISERROR statements within the procedure, however there is no standard that says that you need to do this. In fact, the documentation suggests that you are free to pass arbitrary values back to the calling batch according to what went wrong with the execution of the process.

We can show that even sp_ExecuteSQL returns the error code generated by an error, or it returns 0, if it was just a warning. If it is just a warning, it carries on executing, of course, and its return code is 0 if it is subsequently successful, or the error code of the failure if it wasn’t.

Although the simple stored procedures that most of us write on a day-to-day basis do not require much use of a RETURN code, the value of doing so soon kicks in when we start doing more complex transaction-based processing.

Let’s start by returning a code that represents an error number.

You’ll notice that there is no need to add a RETURN 0 at the end of your procedure because this is done automatically. If you reach the end of the batch, SQL Server reckons you’ve won and so returns 0. If you try to execute…

…instead of…

…you will find that a NULL is returned, along with an ‘invalid object name’ error. Why? It abandons the procedure rather than obey the TRY…CATCH construct. This is because we have triggered an object name resolution error during statement-level recompilation (the stored procedure compiled without a hitch, thanks to deferred name resolution). SQL Server can’t recover the batch from this error and aborts the execution entirely with a NULL. These, as Holmes remarked, are deep waters. To be more precise:

• TRY…CATCH does not trap warnings or informational messages that have a severity of 10 or lower.
• TRY…CATCH can only operate with a running process. This means, for example, that errors which have a severity of 20 or higher that stop the SQL Server Database Engine task processing, for the session, can’t be trapped. This also applies to Attentions, such as client-interrupt requests or broken client connections, and when the session is ended by a system administrator by using the KILL statement. If there are compile errors, such as syntax errors, that prevent a batch from running at all, then it will never get to the TRY …CATCH statement. This also happens if there is an error in resolving object names during any recompilation.

Code Smells and the RETURN value

The RETURN value should only be used to communicate the success or failure of the operation performed, and the reason for it. However, there was a time before OUTPUT parameters that the RETURN value was the only easy way to pass any sort of integer values back to a batch.

When backed into a corner, any grey-muzzled database developer will admit to using the RETURN code for doing this. Now we have no need to turn a blind eye to this SQL Code Smell. When you are passing values from a procedure, you can have as many OUTPUT parameters as you like in a rich panoply of data types, and name them in a way that even the dullest or most inexperienced team-member can figure out.

However, it is best to maintain the convention of returning errors and problems, and the RETURN value is the obvious place to do it. There will be positive integers corresponding to an error value, a NULL if the procedure failed irretrievably, or negative values for application-level process problems.

In a typical batch, several stored procedures are executed in sequence, but the flow of control varies according to what happens in each procedure. Bad things can happen, and you need to react accordingly. Let’s take, as an example, a procedure that INSERTs into a table; it needs to return an appropriate value if the process fails. If, for example, it turned out to be a duplicate entry, the procedure should report back to the calling batch accordingly, explaining the violation of the business rules. However, it may fail for an entirely different reason, such as a deadlock or because disk space has run out. Each of these problems may require a different solution for the calling batch or application. The procedure attempting the insertion needs only to return the appropriate error. It is up to the process that called the procedure to react accordingly.

As an example of reacting to an error passed back by a RETURN, there is the unfortunate occasion when your process is selected as a deadlock victim:

'Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction' (Msg 1205).

Of course, it should really say, ‘wait a short while and rerun the transaction‘. It is simple, when dealing with a process that is occasionally liable to deadlock, to start a transaction, call the procedure, catch error 1205 in a RETURN from the procedure, roll back the transaction, wait for a short interval and retry (see Defensive Error Handling)

Another use for RETURN codes is to return negative numbers for application ‘process’ problems, such as ‘customer currently suspended’, ‘credit limit exceeded’, ‘file note on account’ or ‘bank transfer rejected’. Although positive numbers are reserved for SQL Server errors, you can use negative numbers for a world of application process errors.

Here is a simple example to see whether a city exists in the database. It uses positive numbers for SQL Server errors, and negative numbers for process problems (just the fact that the city doesn’t exist in this example). These process problems are usually best handled in the application so it is far simpler to pass back an integer and let the application handle the reaction (such as the prompt on the form, in the appropriate language).

This all may seem a bit messy for your beautiful, neat code, but the only RETURN keywords you need within the body of the procedure are those that indicate failure, unless you wish to abort a procedure at some point because there is nothing more to do to achieve success. If a procedure reaches the END, it has won, and so returns zero automatically without you needing to tell it.

Conclusions

A stored procedure should inform the process that called it whether it was successful. Stored procedures return an integer value which ought to be captured and checked by the SQL batch or application that called it. Success is signaled by the value zero (0).

However, success can mean many things. A procedure can be entirely error-free, yet it could have failed in terms of the business process. By convention, positive numbers in return values are the messages IDs of SQL Server errors, leaving you free to assign negative values for whatever application process problems you hit.

There are four SQL code smells associated with RETURN, in other words coding practices that merit a check or review:

1. A non-zero RETURN code isn’t passed back to inform the caller of a stored procedure when an error occurs.
2. A RETURN keyword is used without an integer parameter. (BP0016)
3. Failing to react appropriately to the value that is returned from the stored procedure when an error happens
4. Using RETURN to pass back values as part of a process, such as the number of purchases on a given day, rather than the success or failure of the process.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more