Microsoft® SQL Server 2012 Unleashed

Returning Procedure Status

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 -1 and -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 ...