INF: Return Value Precedence in Stored Procedures

 Last reviewed: April 28, 1997
Article ID: Q100830

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

With SQL Server version 4.2, stored procedures return an integer value called a return status. This value can indicate normal operation or an error during stored procedure execution. User-defined return status values can also be returned by use of the RETURN() Transact-SQL statement. This article discusses which value is returned to the caller when several conditions that could result in different return status values occur during execution of a stored procedure.

MORE INFORMATION

A return value can be set to a non-zero value by one of three failure conditions:

  • A runtime failure occurs that terminates execution of the stored procedure. An example of this is an arithmetic overflow or deadlock.
  • A runtime error occurs that permits the continued execution of the stored procedure. An example of this is a unique key violation during an insert.
  • The stored procedure is exited with the RETURN() statement.

As documented in the Transact-SQL "Users Guide," if multiple errors occur during execution of a stored procedure, the return status value corresponds to the error that generated the status with the highest absolute value. For example, if an INSERT failed due to a uniqueness violation, the status would be set to -4. If the batch subsequently failed due to a deadlock (usually causing the status to be set to -3), -4 would be returned as the status value.

User-defined status values set by RETURN() are an exception to the above rule. The value specified in the RETURN() statement is always returned from the stored procedure when the procedure is exited due to the RETURN(). It is important to note that if an error occurs that prematurely terminates execution of the stored procedure (a deadlock for instance), subsequent RETURN() statements will not be executed and the status value with the highest absolute value will be returned.

Additional query words: Transact-SQL DB-Library remote stored procedure
Keywords : kbprg SSrvProg SSrvServer SSrvTrans
Version : 4.2
Platform : OS/2

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.