Return Status

Stored procedures report a return status that indicates whether or not they completed successfully, and if they did not, the reasons for failure.

This value can be stored in a variable when a procedure is called, and used in future Transact-SQL statements. System-defined return status values for failure range from -1 through -99; you can define your own return status values outside this range.

declare @status int 
execute @status = byroyalty 50
select @status

The execution status of the byroyalty procedure is stored in the variable @status. “50” is the supplied parameter, based on the royaltyper column of the titleauthor table. This example prints the value with a select statement; later examples use this return value in conditional clauses.

• Reserved Return Status Values
  SAP ASE reserves 0, to indicate a successful return, and negative values from -1 through -99, to indicate the reasons for failure.
• User-Generated Return Values
  You can generate your own return values in stored procedures by adding a parameter to the return statement. You can use any integer outside the 0 through -99 range.