Most programming languages are able to pass a status code to the caller of a function or subroutine. A value of
0 generally indicates that the execution was successful. SQL Server stored procedures are no exception.
SQL Server automatically generates an integer status value of
0 after successful completion of a stored procedure. If SQL Server detects a system error, a status value between
-99 is returned. You can use the
RETURN statement to explicitly pass a status value less than
-99 or greater than
0. The calling batch or procedure can set up a local variable to retrieve and check the return status.
In Listing 24.17, the stored procedure returns the year-to-date sales for a given title as a result set. If ...