Neglecting to Use, or Misusing, the RETURN Keyword (BP016)
Phil Factor demonstrates the correct use of the RETURN keyword in stored procedures or batches, to pass back a non-zero RETURN code to the calling process, informing it of the error. He also explains some of its misuses.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
raiserror('HELP, I''m trapped in this batch!',5,1) /* Msg 50000, Level 5, State 1, Line 25 HELP, I'm trapped in this batch! */ DECLARE @Return int EXECUTE @Return= sp_executeSQL N'raiserror(''HELP, I''''m trapped in this batch!'',16,1)' SELECT @Return --returns 50000 (user-defined error). But what if we do a warning instead of an error? DECLARE @Return int EXECUTE @Return= sp_executeSQL N'raiserror(''HELP, I''''m trapped in this batch!'',5,1)' SELECT @Return --returns 0 because it was only a warning SELECT * FROM dbo.MissingTable /* Msg 208, Level 16, State 1, Line 40 Invalid object name 'dbo.MissingTable'. */ DECLARE @Return int EXECUTE @Return= sp_executeSQL N'SELECT * FROM dbo.MissingTable' SELECT @Return --returns 208 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE #TryoutProcedure AS BEGIN BEGIN TRY SELECT 1 / 0; --deliberately trigger a divide by zero END TRY BEGIN CATCH RETURN Error_Number(); --return the error END CATCH; END; GO DECLARE @Return INT; EXECUTE @Return = #TryoutProcedure; --execute our sample procedure SELECT @Return IF Coalesce(@Return,0) <> 0 SELECT * FROM sys.messages --and see if the error was passed back WHERE message_id = @Return AND language_id = 1033; |
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…
|
1 |
SELECT * FROM dbo.MissingTable |
…instead of…
|
1 |
SELECT 1 / 0; |
…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…CATCHdoes not trap warnings or informational messages that have a severity of 10 or lower.TRY…CATCHcan 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 theKILLstatement. If there are compile errors, such as syntax errors, that prevent a batch from running at all, then it will never get to theTRY …CATCHstatement. 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE #HowManylettersInWord @AString nvarchar(2000) AS /* never do this. This is a code smell */ BEGIN RETURN (PATINDEX('%[^-a-z]%',@AString+'|' COLLATE Latin1_General_CI_AI)) END; /* tempting. If only the correct way was as slick! */ GO DECLARE @letters int EXECUTE @letters= #HowManylettersInWord 'predestination and science'; --execute our sample procedure SELECT @letters EXECUTE @letters= #HowManylettersInWord 'level-crossing gates'; --execute our sample procedure SELECT @letters |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE adventureworks2016 GO CREATE PROCEDURE #CheckContactCity (@cityName VARCHAR(50)) AS BEGIN DECLARE @CityExists int BEGIN try SELECT @CityExists = CASE when EXISTS (SELECT * FROM adventureworks2016.Person.Address WHERE City = @cityName) THEN 1 ELSE 0 end END TRY BEGIN CATCH RETURN Error_Number(); <a id="post-505042-_Hlk15977359"></a>--return the error as a positive integer END CATCH IF @CityExists= 0 RETURN -100 --you've chosen this to mean 'city doesn't exist END Go --now test it out DECLARE @Return INT; EXECUTE @Return = #CheckContactCity 'Denver'; --execute our sample procedure SELECT @Return --returns zero 'city does exist' EXECUTE @Return = #CheckContactCity 'fougasse'; --execute our sample procedure SELECT @Return --returns -100 'city doesn't exist |
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:
- A non-zero
RETURNcode isn’t passed back to inform the caller of a stored procedure when an error occurs. - A
RETURNkeyword is used without an integer parameter. (BP0016) - Failing to react appropriately to the value that is returned from the stored procedure when an error happens
- Using
RETURNto 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
You may also like
-
Article
Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)
Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.
-
Webinar
More Gems from the SQL Prompt Treasure Chest
Join Microsoft MVP Grant Fritchey and Owen Standage from the Redgate SQL Prompt Development Team to discover even more of the features you may not be using and become the captain of your SQL Prompt ship.
-
Article
Why you Should Avoid Using the @@IDENTITY Function
Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function, and suggests less error-prone ways to get the latest identity values used in a table.
-
Article
SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)
If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere.
-
