Return Status Values

Stored procedures can return an integer value called a return status. This status can indicate that the procedure completed successfully, or it can indicate the reason for failure. SQL Server has a defined set of return values; users can also define their own values. Here's an example of a batch that returns a status:

DECLARE @status int
EXECUTE @status = au_info @lastname = Ringer, @firstname = Anne
SELECT status = @status

The execution status of the au_info procedure is stored in the variable @status.

Later examples use the return value in conditional clauses.

Reserved Return Status Values

SQL Server reserves 0 to indicate a successful completion and negative values in the range from -1 through -99 to indicate various reasons for failure. The failure values -1 through -14 are currently in use:



Value	Meaning

0	Procedure was executed successfully
-1	Object missing
-2	Datatype error occurred
-3	Process was chosen as deadlock victim
- 4	Permission error occurred
-5	Syntax error occurred
- 6	Miscellaneous user error occurred
-7	Resource error, such as out of space, occurred
- 8	Non-fatal internal problem encountered
-9	System limit was reached
-10	Fatal internal inconsistency occurred
-11	Fatal internal inconsistency occurred
-12	Table or index is corrupt
-13	Database is corrupt

-14	Hardware error occurred

The values -1 through -14 correspond to error severity levels 10 through 24. The values -15 through -99 are reserved for future use by SQL Server. For more information about severity levels, see the Microsoft SQL Server Administrator's Companion.

If more than one error occurs during execution, the status with the highest absolute value is returned.

User-generated Return Values

Users can generate their own return values in stored procedures by adding a parameter to the RETURN statement. All integers other than the reserved values from 0 through -99 can be used.

The following example returns 1 when an author has a valid contract and returns 2 in all other cases:

CREATE PROC checkcontract @param varchar(11)
AS
IF (SELECT contract FROM authors WHERE au_id = @param) = 1
    RETURN 1
ELSE
    RETURN 2

The following stored procedure calls checkcontract and uses conditional clauses (IF...ELSE) to check the return status:

CREATE PROC get_au_stat @param varchar(11)
AS
DECLARE @retvalue int
EXECUTE @retvalue = checkcontract @param
IF (@retvalue = 1)
    PRINT 'Contract is valid'
ELSE
    PRINT 'There is not a valid contract'

Here are the results when you execute get_au_stat with the au_id of an author with a valid contract:

get_au_stat '807-91-6654'
Contract is valid