Most programming languages have the ability to pass a status code to the caller of a function or a subroutine. A value of 0 generally indicates that the execution was successful. SQL Server is no exception.
SQL Server will automatically generate an integer status value of 0 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 0. 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.
--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.
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.
|-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|