Trapping Errors and Implementing Error Handling

When working with T-SQL batch processing, errors will occur. You can either learn to handle them or let them handle you. That means when writing your T-SQL scripts, you need to ask yourself, "What should the SQL code do if something goes wrong?" If your batch is a multi-step process and something goes wrong in the middle, should the batch reverse the previous statements, just end, or continue as if nothing happened? Learning how to detect, display, and respond to errors that might occur is very important to the programming process. In this section, you will learn how to use the available global variables that return error information in your SQL scripts.

Communicating Error Messages

When an error occurs during the execution of T-SQL code, the server will determine what error has occurred, assign the appropriate number, and then display the message describing that error. An error number uniquely identifies each error message that the server knows about. Each of these descriptions is kept in the sysmessages table. The following SQL statement will display all the known error messages in the sysmessages table:

use master
select * from sysmessages

Results:

error       severity dlevel Description

----------- -------- ------ ------------------------------
1           10       0      Version date of last upgrade:
21          10       0      Warning: Fatal error %d occurr
102         15       0      Incorrect syntax near '%.*ls'.
103         15       0      The %S_MSG that starts with '%
104         15       0      ORDER BY items must appear in
105         15       0      Unclosed quotation mark before
106         16       0      Too many table names in the qu
…
21510       10       0      Data changes are not allowed w
21511       10       0      Neither MSmerge_contents nor M

(3782 row(s) affected)

When an error does occur, it is associated with the appropriate error number, and then the server reads the sysmessages table to retrieve the correct text description of that error to display. This process is called raising an error. When an error is raised, the server provides several attributes to assist in determining where and why an error has occurred. These attributes are

• Error Number—A unique number that identifies the error.

• Message String—A text string that provides diagnostic information about the cause of the error.

• Severity—Specifies the severity of the error. Low severity errors are usually informational, whereas high severity errors indicate problems that should be addressed immediately.

• State Code—Some errors can be raised at multiple locations in the source code for SQL Sever. Each location where an error code is raised assigns a unique state code. This state code can be used by a Microsoft support engineer to find the location in the source code where that error code is being raised.

• Procedure Name—The name of the stored procedure in which the error occurred, if available.

• Line Number—The line number in the stored procedure where the error occurred.

To determine the current error, you would use the @@ERROR variable or system function. This variable returns a zero if the last T-SQL statement executed successfully. If the last statement generated an error, @@ERROR returns the error number. The value contained in @@ERROR changes at the completion of each T-SQL statement. The @@ERROR variable can be displayed by using either the SELECT statement or the PRINT statement as shown in the following example:

print 'Error #: '+ str(@@error)
select @@ERROR

Results:

Error #:          0

-----------
0

(1 row(s) affected)

Or, you can use @@ERROR to identify that an error occurred and take action depending on the error. The following example shows how to use the IF…ELSE statement to check for a specific error:

If @@error = 547
   Print 'A check constraint violation has occurred'

Raising an Error Message

In addition to using the error messages that are included with SQL Server, you can use your own error messages when writing T-SQL code in stored procedures and triggers. You can raise an error by using the RAISERROR statement. This statement enables you to return a user-defined error message and set the system flag to record that an error has occurred. Using RAISERROR, you can either retrieve an entry from the sysmessages table or build a message yourself. After the message is defined, it is sent back to the client as a server error message. The syntax for the RAISERROR is as follows and Table 11.4 lists each of the arguments and their descriptions.

Raiserror ( { msg_id | msg_str} { ,severity, state}
    [,argument[,…n]])
    [With option [,…n]]

Any error message can contain placeholders that will be replaced with values in the RAISERROR argument list. A placeholder is specified by a percent sign (%) followed by one of the following:

• d—Placeholder for a number

• ls or *ls—Placeholder for a string

The following example shows how to use the RAISERROR statement to display a custom error message:

Declare @dbid int, @dbname nvarchar(128)
Set @dbid = db_id()
Set @dbname = db_name()
Raiserror ('The current database ID is: %d, the database name is: %ls.',
           16,1,@dbid, @dbname)

Results:

Server: Msg 50000, Level 16, State 1, Line 4
The current database ID is: 6, the database name is: Northwind.

The RAISERROR statement is very useful when detecting errors in your SQL logic or in the data. Neither one of these conditions will trigger an error from the server. In the next section, you will see how to create code that will detect these types of errors as well as system-generated errors.

Creating an Error Message

In addition to using the error messages that are defined in SQL Server, or creating a custom error message displayed with the RAISERROR for a single event, you can create custom error messages and store them in the sysmessages table to be used by any SQL program that will execute on the server. This also enables you to define custom error messages once and use them in multiple locations in your SQL programs. As always, there are two ways that you can define a new error message. You can use the Enterprise Manager, or use the sp_addmessage system stored procedure to add messages to the sysmessages table.

Using Enterprise Manager to Create an Error Message

To define a new error message using the Enterprise Manager, expand the SQL Server Group folder to display the database servers. Then right-click on the database server you want to create the new error message. To display the Manage SQL Server Messages dialog box, select All Tasks, Manage SQL Server Messages from the pop-up menu, as shown in Figure 11.1.

In the Manage SQL Server Messages dialog, click on the Messages tab to display any customer error messages already defined, as shown in Figure 11.2.

Now, to define a new error message, click the New button to display the New SQL Server Message dialog box as shown in Figure 11.3.

In this dialog, you can specify the error number (which must be greater than 50000), the severity level, the language, and the actual message. Change the message text from 'User-defined message' to 'This is a demo of Defining an Error Message'. Now, click OK to save this new message and you will see it displayed in the Manage SQL Server Messages dialog (see Figure 11.4).

You can now use your newly defined error in the RAISERROR statement as shown:

Raiserror (50002,0,1)

Results:

This is a demo of Defining an Error Message

Finally, you can also define a new error message using the stored procedure sp_addmessage. The syntax for this procedure is

Sp_addmessage [ @msgnum = ] msg_id,
    [@severity = ] severity,
    [@msgtext = ] 'msg'
    [,[@lang = ] 'language']
    [,[@with_log = ] 'with_log']
    [,[@replace = 'replace']

The following example shows the previous error message being defined using this stored procedure:

Use Master
Sp_addmessage 50002,
    10,
    'This is a demo of Defining an Error Message2',
    @replace='REPLACE'

Finally, if you want to delete an error message that you have previously defined, you would use the sp_dropmessage stored procedure as shown in the following example:

Use Master
Sp_dropmessage 50002

Coding Standard Error Handling Methods

Standard error handling is done using many of the programming techniques you have learned earlier today. For every statement that you execute in a SQL script, you must check the @@ERROR variable to see whether an error has occurred. By using the @@ERROR variable, you can

• Detect specific errors

• Conditionally exit a stored procedure or trigger

• Determine whether to commit or rollback a transaction

• Use local variables to check several statements at once

Depending on the type of processing the stored procedure or trigger is performing, you could check for and handle errors differently. If a procedure is not updating any tables, you could check for errors after each statement and store the value of any errors found to return to the calling routine. This will work only if you do not need to reverse any of the commands that were processed. The following is an example of this type of stored procedure:

Use Northiwnd
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
            @MaxQuantity INT OUTPUT
AS
DECLARE @ErrorSave INT
SET @ErrorSave = 0

SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR

SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR

RETURN @ErrorSave

The entire procedure will be executed whether or not an error occurs. This is okay as long as no data is invalid because of the error. This stored procedure could be modified to exit and return the error value anytime a non-zero error is detected by making use of the BEGIN…END statement logic. The following example shows those changes:

Use Northiwnd
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
            @MaxQuantity INT OUTPUT
AS

SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

IF (@@ERROR <> 0)
BEGIN
    RETURN (@@ERROR)
END

SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

IF (@@ERROR <> 0)
BEGIN
    RETURN (@@ERROR)
END

Finally, if you have a SQL statement that modifies data, such as an UPDATE statement, and you are using transaction processing, you might want to ROLLBACK or COMMIT the transaction depending on the value of the @@ERROR variable. The following is an example of this type of error processing:

Use northwind
Begin Transaction
Update Products
Set UnitsInStock = UnitsInStock - 1
Where ProductID = 1
If @@Error <> 0
Begin
    Rollback Transaction
    Return (@@ERROR)
End
Insert [order details](orderid, productid, unitprice, quantity, discount)
       Values(1002,1,3.50,1,.25)
If @@Error <> 0
Begin
    Rollback Transaction
    Return (@@Error)
End
Commit Transaction
Return (0)

This routine updates the Products table to decrease the quantity of a product. If this update generates an error, you do not want to insert a new order row. If an error is encountered anywhere in the routine, a ROLLBACK TRANSACTION statement is executed and the error number is returned to the calling routine. If there was no error, a COMMIT TRANSACTION is executed and a 0 is returned.

This is only the tip of the iceberg when it comes to error processing. However, you can see that you need to check for errors after every SQL statement that you execute.