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.
Here is an example of a batch that uses the form of the execute statement that returns the status:
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.
