Returning Procedure Status

Most programming languages have the ability to pass a status code to the caller of a function or a subroutine. A value of generally indicates that the execution was successful. SQL Server is no exception.

SQL Server will automatically generate an integer status value of after successful completion. If SQL Server detects an 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 . The calling batch or procedure can set up a local variable to retrieve and check the return status.

In Listing 28.15, you want to return the year-to-date sales for a given title as a resultset. If the title does not exist, you do not want to return an empty resultset. Therefore, you perform a check inside the procedure and return the status value -101 if the title does not exist.

In the calling batch or stored procedure, you need to create a variable to hold the return value. The variable name is passed after the EXECUTE statement.

Listing 28.15 Returning a Status Code from a Stored Procedure

 --Create the procedure CREATE PROC ytd_sales2 @title varchar(80) AS IF NOT EXISTS (SELECT * FROM titles WHERE title = @title)     RETURN -101 SELECT ytd_sales    FROM titles    WHERE title = @title RETURN GO -- Execute the procedure DECLARE @status int EXEC @status = ytd_sales2 'Life without Fear' IF @status = -101     PRINT 'No title with that name found.' 

RETURN values can also be passed back and captured by the client application through ADO, ODBC, OLE DB, and so on.

SQL Server Internal Status Codes

If a stored procedure terminates unexpectedly, SQL Server returns a status code. The values -1 to -99 are reserved by SQL Server, and -1 to -14 are currently in use. Table 28.1 lists the return codes currently in use by SQL Server and their meanings.

Table 28.1. SQL Server Return Codes

Status Code	Meaning
	Successful execution
-1	Object missing
-2	Datatype error occurred
-3	Process chosen as a deadlock victim
-4	Permission error occurred
-5	Syntax error occurred
-6	Miscellaneous user error occurred
-7	Resource error occurred, such as out of space
-8	Nonfatal internal problem encountered
-9	System limit reached
-10	Fatal internal inconsistency occurred
-11	Fatal internal inconsistency occurred
-12	Table or index corrupted
-13	Database is corrupt
-14	Hardware error