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.
Status Code | Meaning |
---|---|
0 | 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 |