INF: Return Value Precedence in Stored Procedures

ID: Q100830

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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 SSrvTrans SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 16, 1999