This chapter is from the book
This chapter is from the book
This chapter is from the book
Errors
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, you must save it to a variable if you wish to process it further after checking it.
If you want to write robust code that runs for years without having to be reengineered, make a habit of checking @@ERROR often in your stored procedures, especially after data modification statements. A good indicator of resilient code is consistent error checking, and until Transact-SQL supports structured exception handling, checking @@ERROR frequently is the best way to protect your code against unforeseen circumstances.
Error Messages
The system procedure sp_addmessage adds custom messages to the sysmessages table that can then be raised (returned to the client) by the RAISERROR command. User messages should have error numbers of 50,000 or higher. The chief advantage of using SQL Server's system messages facility is internationalization. Because you specify a language ID when you add a message via sp_addmessage, you can add a separate version of your application's messages for each language it supports. When your stored procedures then reference a message by number, the appropriate message will be returned to your application using SQL Server's current language setting.
RAISERROR
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log. RAISERROR can reference an error message added to the sysmessages table via the sp_addmessage system procedure, or you can supply it a message string of your own. If you pass a custom message string to RAISERROR, the error number is set to 50,000; if you raise an error by number using a message ID in the sysmessages table, @@ERROR is assigned the message number you raise. RAISERROR can format messages similarly to the C printf() function, allowing you to supply your own arguments for the error messages it returns.
Both a severity and a state can be specified when raising an error message with RAISERROR. Severity values less than 16 produce informational messages in the application event log (when logged). A severity of 16 produces a warning message in the event log. Severity values greater than 16 produce error messages in the event log. Severity values up through 18 can be raised by any user; severity values 19 through 25 are reserved for members of the sysadmin role and require the use of the WITH LOG option. Severity values of 20 and higher are considered fatal and cause the client connection to be terminated.
State has no predefined meaning to SQL Server; it's an informational value that you can use to return state information to an application. Raising an error with a state of 127 will cause the ISQL and OSQL utilities to set the operating system ERRORLEVEL variable to the error number returned by RAISERROR.
The WITH LOG option copies the error message to the NT event log (if SQL Server is running on Windows NT, Windows 2000, or Windows XP) and the SQL Server error log regardless of whether the message was defined using the with_log option of sp_addmessage. The WITH NOWAIT option causes the message to be returned immediately to the client. The WITH SETERROR option forces @@ERROR to return the last error number raised, regardless of the severity of the error message. See Chapter 7 for detailed examples of how to use RAISERROR(), @@ERROR, and SQL Server's other error-handling mechanisms.