|
Category: SQL Server
view more software Tips and Tricks
Updated: 08/23/2012 06:08 AM
Author: Shiju Mathews Status: Resolved. |
|
All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0. With some occasional exception, the system stored procedures that Microsoft ships with SQL Server return 0 to indicate success and any non-zero value indicates failure. While there is no law that requires you to follow the same convention for your stored procedures, my strong recommendation is that you use return values solely to indicate success/failure. If you want to return data such as the id for an inserted row, number of affected rows or whatever, use an OUTPUT parameter instead. It follows from the fact that a blank RETURN may return 0, even if there has been an error during execution, that you should be careful to return an explict value yourself if an error occurs in the procedure. There is one situation when a stored procedure does not return any value at all, leaving the variable receiving the return value unaffected. This is when the procedure is aborted because of a scope-aborting error. We will look more into this later. There is also one situation when the return value is NULL: this happens with remote procedures and occurs when the batch is aborted on the remote server. (Batch-abortion is also something we will look into more later on.) |