Handling Errors

An important element of any program you write is the error-checking section. During software development, it is a good programming technique to check for errors in your code, and abort the execution of the program or trap the error when it is found. If the program crashes, there's a high probability that it crashed because you neglected to check or trap an error.

Transact-SQL provides twoelements that allow us to check and throw errors programmatically. These elements are the @@ERROR parameterless(or niladic) system function and the RAISERROR statement.

The @@ERROR system function returns the error code (an integer different from 0) of the last statement executed, if there was an error. On the other hand, if the last statement was executed successfully, @@ERROR returns 0. Be aware that this value changes from one statement to the next one; hence, you must check this value right after the statement is executed.

RAISERROR is used to explicitly throw an error. You can either use an ad hoc message or a message stored in the Sysmessages system table (all SQL Server error messages are stored in Sysmessages). You can add your own messages to this system table through the sp_addmessage system stored procedure, and to delete messages, through sp_dropmessage. Notice that when creating a user-defined message with sp_addmessage, you must specify a message ID greater than 50,001 (message IDs less than 50,000 are reserved by SQL Server).

This is the syntax of RAISERROR:

					RAISERROR (msg_id | msg_text, severity, state) WITH option
				

The first parameteris the message ID or the message text. If you specify a message ID, you need to have previously created a user-defined message with sp_addmessage. If you want to use an ad hoc message, the message text can have up to 400 characters. The second parameter is the severity level of the error, which is a number between 0 and 25 (severity levels greater than 20 must be used by system administrators for critical errors). If the severity level falls in the range of 0 through 10, it's considered an informational message. Then, severity levels from 10 to 19 are used for trappable errors, and from 20 to 25 for critical errors (which close the connection after the client receivesthe error message).

The third parameter, the state of the error, is an integer between 0 and 127 which, by the documentation, isn't significant to SQL Server. Finally, there are two options (either can be used) in the last parameter, which are optional:

• LOG—Stores the error information in the SQL Server error log and in the NT Application log. This option must be specified when using severity levels higher than 19.

• NOWAIT—This option sends the error message immediately to the client application.

After executing RAISERROR, @@ERROR returns the value of the message IDof the error or, if you use an ad hoc message, it will return 50,000.

Listing 8.26 demonstrates the use of sp_addmessage, @@ERROR, and RAISERROR.

Code Listing 8.26. Using @@ERROR and RAISERROR

					
USE Northwind
GO


sp_addmessage 50001,11,'An error occurred'
GO


CREATE PROC generateerror
AS
RAISERROR (50001,11,1) WITH LOG
SELECT @@ERROR
GO

generateerror
(1 row(s) affected)

Server: Msg 50001, Level 11, State 1, Procedure generateerror, Line 4

An error occurred

-----------
50001

(1 row(s) affected)