Lesson 2: Detecting and Reacting to Errors

Estimated lesson time: 30 minutes

Implementing appropriate error handling is one of the most important tasks when developing T-SQL scripts, stored procedures, and triggers. In this case, appropriate error handling means that some routines (usually read-only) might not require the same error handling that more important transactional routines require.

Techniques for Detecting and Reacting to Errors

There are three ways to detect and react to errors in SQL Server 2005:

Querying the @@ERROR function.
Setting the XACT_ABORT session option.
Implementing structured error handling using TRY…CATCH blocks.

The ability to use structured error handling is a new feature in SQL Server 2005 and more or less renders the first two ways obsolete. However, you will still find code that uses the first two techniques.

Error Handling with @@ERROR

The @@ERROR function returns the ID (number) of the error message that was caused by the previously executed statement. If the previous statement did not cause an error, the function will return 0. Because it only checks the previous statement, you will need to implement a check against this function after every statement that can cause an error to occur-that is, after almost every statement. Below you can see an example of a transaction using the @@ERROR function to handle errors.

 BEGIN TRAN;    INSERT T1 (Col) VALUES (1);    IF(@@ERROR <> 0)          GOTO ErrorHandler;    INSERT T1 (Col) VALUES (2);    IF(@@ERROR <> 0)          GOTO ErrorHandler;    INSERT T1 (Col) VALUES (3);    IF(@@ERROR <> 0)          GOTO ErrorHandler; COMMIT TRAN; RETURN; ErrorHandler: ROLLBACK TRAN; RETURN;

As you can see, the code gets polluted by all the checks against the @@ERROR function. This type of error handling renders code that is hard to read and maintain and is also prone to bugs. One problem is that it is easy to forget a check against the @@ERROR function and another problem is the use of GOTO statements to transfer control to the error handling part of our script. As you know, GOTO isn't exactly your friend if you want to develop maintainable code.

Error Handling with XACT_ABORT

The XACT_ABORT session option is another way to handle errors. Consider the following batch against the table "CREATE TABLE Test.ATable (ID INT PRIMARY KEY);" with the XACT_ABORT option set to OFF (the default setting).

 SET XACT_ABORT OFF; BEGIN TRAN;    INSERT Test.ATable (ID) VALUES (1); INSERT Test.ATable (ID) VALUES (1); -- Fails!    INSERT Test.ATable (ID) VALUES (2); -- Execution continues. COMMIT TRAN;

The result is:

INSERT #1: Succeeds.
INSERT #2: Fails with error 2627, primary key constraint violation, and the statement (that is, only the insert itself) is rolled back.
INSERT #3: Succeeds.
The transaction is committed.

After this batch has completed, the table will contain two rows, one with the value 1, and another one with the value 2. This is the default behavior in SQL Server: a statement that generates an error causes the statement itself to be rolled back, but any open user-defined transaction stays open and the batch continues execution.

Change the batch slightly by adding the SET XACT_ABORT ON; option at the beginning of the batch. (Note that this is a session-level option.)

 SET XACT_ABORT ON; BEGIN TRAN;    INSERT Test.ATable (ID) VALUES (1);    INSERT Test.ATable (ID) VALUES (1); -- Fails!    INSERT Test.ATable (ID) VALUES (2); -- Not executed. COMMIT TRAN;

When you execute this batch, the result is different:

INSERT #1: Succeeds.
INSERT #2: Fails with error 2627, primary key constraint violation, the entire transaction is rolled back, and the execution of the batch is terminated.

Even though this behavior is simple and crude, it is actually very useful. Setting XACT_ABORT ON simply means: "If anything goes wrong, stop execution and roll back any open transaction (in the current connection)." So, if you don't need to react to the error (log it to an error table, for example) in the database, you can make good use of the XACT_ABORT ON setting. The obvious advantage with this solution is ease of use. If anything goes wrong, you know that the batch will be terminated and the transaction rolled back. Note that you can turn off XACT_ABORT temporarily in, for example, a stored procedure, and then turn it back on again.

The setting actually exists specifically for use with distributed transactions involving other database instances, such as through linked servers. These transactions require the XACT_ABORT setting to be set to ON, basically because they have a higher risk of failing (due to network problems, for example).

If you use XACT_ABORT ON with stored procedures, the entire batch, not only the stored procedure that caused the error, will still stop execution and the transaction will be rolled back.

Here is an example of XACT_ABORT ON with stored procedures against the same table structure as in the previous examples. Note that the XACT_ABORT option is set to OFF and then turned on in the stored procedure:

 CREATE PROC Test.sp1 AS BEGIN    SET XACT_ABORT ON;    BEGIN TRAN;          INSERT Test.ATable (ID) VALUES (1);    COMMIT TRAN; END GO TRUNCAT TABLE Test.ATable; SET XACT_ABORT OFF; BEGIN TRAN;    EXEC Test.sp1;    EXEC Test.sp1; -- Fails!    EXEC Test.sp1; -- Not executed. COMMIT TRAN;

The result of the previous batch is:

Test.sp1 Execution #1: Succeeds.
Test.sp1 Execution #2: Fails with error 2627, primary key constraint violation, the entire transaction is rolled back, and the execution of the batch is terminated.

Structured Error Handling Using TRY/CATCH

The ability to use try/catch type error handling in SQL Server 2005 is a great improvement to the product. This makes it simple to handle errors without polluting your code with lots of "IF @@ERROR" statements. This is brief explanation of the try/catch-block implementation in SQL Server 2005:

Any error raised in the try block will cause control to jump to the associated catch block. The error will not be sent to the client application.
An error raised in the catch block will cause the error to be sent to the calling application and the batch will continue execution. However, if the try/catch block is nested inside another try block, control will jump to the closest outer catch block. Note that the behavior relating to nested try/catch blocks is typical for nested stored procedures.

This is dependent on the error's severity level. If the severity level is 20 or greater, SQL Server will terminate the connection, stop execution of the batch, and roll back any open transaction. Errors with severity levels of 20 or greater are referred to as fatal errors. This behavior is not new to SQL Server 2005; the same behavior can be found in earlier versions of the product.
Try/catch blocks cannot be split into multiple batches; the catch block must directly follow the try block.
In the catch block, the transaction can either be committed or rolled back (or left alone).

Execution of a RAISERROR statement inside a try block transfers control to the catch block.

Next, look at an example try/catch block (referring to the same Test.ATable table as before):

 TRUNCATE TABLE Test.ATable; BEGIN TRY    BEGIN TRAN;          INSERT Test.ATable (ID) VALUES (1);          INSERT Test.ATable (ID) VALUES (1); -- Fails!          INSERT Test.ATable (ID) VALUES (2);    COMMIT TRAN; END TRY BEGIN CATCH    ROLLBACK TRAN;    RAISERROR('An error occurred! ', 16, 1); END CATCH

The result of this batch is:

INSERT #1: Succeeds.
INSERT #2: Fails with error 2627, primary key constraint violation, and the statement (that is, only the insert itself) is rolled back. No error message is sent to the calling application.
Execution is transferred to the catch block.
The transaction is rolled back.
The RAISERROR statement is executed and the message is sent to the calling application.
Any statements following the catch block are also executed.

One of the important aspects that you can see in the above sequence of events is that no message is sent to the calling application until the RAISERROR statement is executed in the catch block. If you would remove the RAISERROR statement from the above batch, no error would be sent to the calling application. It would not be aware that an error was encountered. Consider the following example:

 TRUNCATE TABLE Test.ATable; BEGIN TRY    INSERT Test.ATable (ID) VALUES (1); END TRY BEGIN CATCH    -- Doing nothing here :-) END CATCH

If an error is encountered in the try block, no error message will be returned to the client.

How do you return the actual error message to the client when inside the catch block? Unfortunately, there isn't any command in SQL Server 2005 to raise exactly the same error as was caught by the catch block. You can, however, fetch the information about the error using the following functions:

ERROR_NUMBER()
ERROR_MESSAGE()
ERROR_SEVERITY()
ERROR_STATE()

This function can return a state of 0 for some system errors. If you want to raise the same error again using RAISERROR, the 0 has to be replaced with a value between 1 and 127.
ERROR_PROCEDURE()

Returns the name of the stored procedure, trigger, or function where the error was raised. This function returns NULL if the error was not raised inside of any of these object types.
ERROR_LINE()

These functions can be used anywhere inside of a catch block (outside of a catch block, all functions return NULL). You can also execute a stored procedure inside the catch block and use the functions inside of that stored procedure to raise the error again. To reraise the original error, you will need to copy the return values from the above functions into variables and pass the variables to the RAISERROR statement.

One issue you will run into here is that you are not allowed to raise system error messages using RAISERROR. This means that you cannot specify the same error number as the original message; you can only specify the same message text. When executing RAISERROR without specifying an error number, the number will be set to 50000. This can be a problem if you have existing client-side code that checks the error number returned from a SQL execution and reacts to it. In the following example, you send the original error number at the end of the error message text. This is not a perfect solution, but at least it makes it possible to fetch the original error number on the client side if it is required.

 TRUNCATE TABLE Test.ATable; BEGIN TRY    BEGIN TRAN;          INSERT Test.ATable (ID) VALUES (1);          INSERT Test.ATable (ID) VALUES (1); -- Fails!    COMMIT TRAN; END TRY BEGIN CATCH    -- Rollback the tran. and free locks held.    ROLLBACK TRAN;    -- Copy the error details to local variables.    DECLARE @ErrMessage NVARCHAR(2047)          ,@ErrNumber INT          ,@ErrSeverity TINYINT          ,@ErrState TINYINT;    SELECT @ErrMessage = ERROR_MESSAGE() + N' #' +                CAST(ERROR_NUMBER() AS NVARCHAR(10))          ,@ErrNumber = ERROR_NUMBER()          ,@ErrSeverity = ERROR_SEVERITY()          ,@ErrState = ERROR_STATE();    -- Re-raise the original error (or atlest something resembling it...)    RAISERROR(@ErrMessage, @ErrSeverity, @ErrState); END CATCH

There is one thing in particular to be aware of when executing this example. The error message returned from this execution will look like this in SQL Server Management Studio:

 Msg 50000, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK__ATable__5AEE82B9'. Cannot insert duplicate key in object 'Test.ATable'. #2627

The original error number has been appended to the end of the error message. The line number (Line) from which this message says the error originated is not the line which produced the original error, but rather the line in the catch block where you execute the RAISERROR statement. You will run into this same issue if you use a stored procedure in the catch block to raise the error message again, in which case the line number would refer to the line in that stored procedure and the procedure part of the error message would be the name of that stored procedure and not the stored procedure that you executed.

The SQL Server 2005 implementation of try/catch has some limitations compared to typical try/catch implementations (as seen in C++, C#, J#, Java, JScript, Visual Basic .NET, and so on).

Only one catch block per try block is supported.
There is no support for a finally block.
There is no command to rethrow a caught error.

There are two more functions related to error handling and transactions. First, the old @@TRANCOUNT function returns the current nesting level in terms of transactions. If you are not in a transaction, it returns 0; if you are nested in one, it returns 1; nested inside two, it returns 2, and so on. The second function is new to SQL Server 2005 and is called XACT_STATE(). This function returns -1, 0, or 1, which refer to the state of the current user-defined transaction.

-1 (uncommittable) means that there is an open user-defined transaction. However, because of the type of error that has occurred, this transaction cannot be committed; it must be rolled back. There are two other specific instances when XACT_STATE() will return -1: one is inside of a trigger (which cannot commit a transaction) and the other one is if you specified SET XACT_ABORT ON.
0 (no transaction) means that there is no open user-defined transaction.
1 (committable) means that there is an open user-defined transaction that can be either committed or rolled back.

SET XACT_ABORT ON; behaves differently when you are inside a try block. The difference is simply that, instead of terminating the batch and rolling back the transaction, control is transferred to the catch block, but the transaction is left uncommittable or doomed (that is, XACT_STATE() returns -1). This means that if XACT_ABORT is turned on, you cannot commit transactions inside a catch block.

Exam Tip

For this exam, it is important to understand the different reactions that SQL Server will have to errors depending on the setting of SET XACT_ABORT, as well as the use or non-use of try/catch blocks.

Creating User-Defined Messages

Previously in this lesson, you used the RAISERROR statement to send error messages back to the client application. When you used RAISERROR, you provided it with an error message in the form of a string, a severity level, and a state. If you issue the same error messages from different stored procedures or triggers, you might be interested in storing the messages in SQL Server to reuse them. You can create, modify, and delete error messages using the following three system-stored procedures:

sp_addmessage
sp_altermessage
sp_dropmessage

The error messages that you create must have a message number of 50001 or higher. Messages are created using the sp_addmessage procedure. You can see which messages are already defined by querying the sys.messages catalog view.

Important

Storage of messages

These messages are stored in the msdb database. If you move a database to a new server, you must make sure that you create the messages used by this database on the new server.

The following is an example of creating a new user-defined message:

 EXEC sp_addmessage @msgnum = 50001 ,@severity = 16    ,@msgtext = N'A customer named %s already exists with the ID %d.'    ,@lang = 'us_english';

This message will get the message number 50001, severity level 16, and the text specified in the @msgtext parameter as its message text. The message is also specified to be in the language us_english. There are two placeholders, %s and %d. The placeholders can be populated by adding extra parameters to the end of the RAISERROR statement. Here is an example that raises the previous error and populates the placeholders:

 RAISERROR(50001, 16, 1, 'Woodgrove Bank', 12902);

The message returned from the statement will look like this in SQL Server Management Studio:

 Msg 50001, Level 16, State 1, Line 1 A customer named Woodgrove Bank already exists with the ID 12902.

The first placeholder is replaced by the first parameter, the second placeholder by the second parameter, and so on; a maximum of 20 placeholders is supported. The name of the placeholder is not arbitrary; it specifies what type of parameter it can be replaced by and how it should be displayed. In this example, %s and %d are used, where %s means that the parameter must be a string and %d means that it must be an integer (signed). For more information about the definition of these placeholders, look up the RAISERROR statement in Books Online.

Creating Localized User-Defined Messages

In the previous example, the message was created for the us_english language. You can also create additional versions of this error message for other languages. The following example creates the same error message for the Swedish language:

 EXEC sp_addmessage @msgnum = 50001 ,@severity = 16    ,@msgtext = N'Kunden %1! existerar redan med ID-nummer %2!.'    ,@lang = 'Svenska';

The error message that is displayed depends on the current connection's language setting. Consider the following batch:

 SET LANGUAGE us_english; RAISERROR(50001, 16, 1, 'Woodgrove Bank', 12902); SET LANGUAGE Svenska; RAISERROR(50001, 16, 1, 'Woodgrove Bank', 12902); SET LANGUAGE Magyar; RAISERROR(50001, 16, 1, 'Woodgrove Bank', 12902);

The first RAISERROR will display the English error message, and the second one will display the Swedish error message. The last example changes the language to Magyar (Hungarian), but because there is no message defined for Hungarian, the us_english message will be shown. A us_english version of the message is required as the base error message; you can only create messages in other languages after you first create a us_english version of the error message. If you look at the message texts specified for us_english and Swedish, you will see that the placeholders are different.

us_english: "A customer named %s already exists with the ID %d."
Swedish: "Kunden %1! existerar redan med ID-nummer %2!"

In the localized version, the placeholders refer to the placeholders in the us_english message. The placeholder %1! refers to the first (1) placeholder in the English message (in this case, %s), and the placeholder %2! refers to the second one (%d). This is because the placeholders might need to be reordered when translating them to a language other than English (although we didn't need to do that for the Swedish version of this error message). You can find the available languages in the catalog view sys.syslanguages.

Writing Error Messages to the Windows Event Log

You can specify that error messages should be written to the Windows event log in three ways:

By adding the parameter @with_log = ‘true' when creating a user-defined message using sp_addmessage.
By adding the WITH LOG option at the end of a RAISERROR statement.
By executing the xp_logevent extended stored procedure.

If you specify the @with_log = ‘true' parameter when you add the us_english version of an error message using sp_addmessage, the error message will always be written to the Windows event log when it is raised using the RAISERROR statement. If you add the WITH LOG option to the RAISERROR statement (as in the following example), only that specific execution of RAISERROR will be written to the Windows event log. See Figure 5-5 for an example.

 RAISERROR(50001, 16, 1, 'Woodgrove Bank', 12902) WITH LOG;

image from book
Figure 5-5: Error message from Windows Event Viewer

Another way to write messages to the Windows event log is by executing the xp_logevent extended stored procedure. The difference from using RAISERROR … WITH LOG is that xp_logevent does not send any error message back to the client application; it only writes the message to the Windows event log.

Lab: Using Try/Catch Blocks

In this lab, you create a stored procedure that uses a try/catch block to insert or update a row in a table. The first exercise will provide an example of unwanted behavior that can occur; the second exercise provides a fix to the problems found in the first exercise.

Important

Lab requirements

You will need to have SQL Server installed before you can complete this exercise. Refer to the Introduction for setup instructions.

Exercise 1: Create an Initial Stored Procedure

In this exercise, you will execute a batch to either insert or update a customer in the Test.Customers table, depending on whether the customer already exists.

Open SQL Server Management Studio and connect to an instance of SQL Server 2005.
In a new query window, type and execute the following SQL statements to create the TestDB database, the Test schema, and the Test.Customers table. (There is a check constraint on the Name column that specifies that the name must be at least five characters long.)
```
 CREATE DATABASE TestDB; GO USE TestDB; GO CREATE SCHEMA Test; GO CREATE TABLE Test.Customers (CustomerID INT PRIMARY KEY ,Name NVARCHAR(50) NOT NULL CHECK(LEN(Name) > 5) ); 
```

Open a new query window and execute the following SQL statements to create a new stored procedure. This stored procedure will be used to save a customer to the database by either inserting or updating data in the Test.Customers table.

 USE TestDB; GO CREATE PROC Test.spCustomerSave @CustomerID INT ,@Name NVARCHAR(50) AS BEGIN    SET NOCOUNT ON;    BEGIN TRAN;          BEGIN TRY                INSERT Test.Customers (CustomerID, Name)                      VALUES (@CustomerID, @Name);          END TRY          BEGIN CATCH                UPDATE Test.Customers SET                      Name = @Name                WHERE CustomerID = @CustomerID;          END CATCH    COMMIT TRAN; END

Open a new query window and execute the following SQL statements to test the stored procedure.

 USE TestDB; GO EXEC Test.spCustomerSave @CustomerID = 1, @Name = 'Woodgrove Bank'; SELECT * FROM Test.Customers; GO EXEC Test.spCustomerSave @CustomerID = 1, @Name = 'Contoso'; SELECT * FROM Test.Customers; GO EXEC Test.spCustomerSave @CustomerID = 1, @Name = '1'; SELECT * FROM Test.Customers; GO EXEC Test.spCustomerSave @CustomerID = 2, @Name = '2'; SELECT * FROM Test.Customers;

The last two executions try to update (the third one) and insert (the fourth one) a new customer with a name that is too short according to the check constraint on the Name column. The update will fail with an error message, but the insert will not; however, it will not be inserted.

Exercise 2: Create an Initial Try/Catch Block

In this exercise, you will execute a batch to either insert or update a customer in the Test.Customers table depending on whether the customer already exists.

Open a new query window and execute the following SQL statements to alter the stored procedure created in Exercise 1, "Create an Initial Stored Procedure." Provide error checking for both the insert and the update. Also check whether the transaction should be committed or rolled back at the end of the stored procedure.

 USE TestDB; GO ALTER PROC Test.spCustomerSave @CustomerID INT ,@Name NVARCHAR(50) AS BEGIN    SET NOCOUNT ON;    SET XACT_ABORT OFF;    BEGIN TRAN;          BEGIN TRY                INSERT Test.Customers (CustomerID, Name)                      VALUES (@CustomerID, @Name);          END TRY          BEGIN CATCH                -- Only update if the error from the insert                -- is a PK violation.                IF(ERROR_NUMBER() = 2627)                      BEGIN                            -- If the UPDATE fails, stop execution                            -- and rollback tran.                            SET XACT_ABORT ON;                            UPDATE Test.Customers SET                                  Name = @Name                            WHERE CustomerID = @CustomerID;                            SET XACT_ABORT OFF;                      END                ELSE                      BEGIN                            ROLLBACK TRAN;                            -- Reraise the orginal error message.                            DECLARE @Msg NVARCHAR(2047)                                  ,@Severity INT                                  ,@State INT;                            SET @Msg = ERROR_MESSAGE();                            SET @Severity = ERROR_SEVERITY();                            SET @State = CASE ERROR_STATE() WHEN 0 THEN 1                                      ELSE ERROR_STATE() END;                            RAISERROR(@Msg, @Severity, @State);                            -- Exit the stored procedure.                            RETURN(1);                      END          END CATCH    COMMIT TRAN; END

Empty the Test.Customers table by executing the TRUNCATE TABLE statement below, and then execute the stored procedure using the following four examples. Run each stored procedure execution separately.
```
 USE TestDB; TRUNCATE TABLE Test.Customers; EXEC Test.spCustomerSave @CustomerID = 1, @Name = 'Woodgrove Bank'; SELECT * FROM Test.Customers; EXEC Test.spCustomerSave @CustomerID = 1, @Name = 'Contoso'; SELECT * FROM Test.Customers; EXEC Test.spCustomerSave @CustomerID = 1, @Name = '1'; EXEC Test.spCustomerSave @CustomerID = 2, @Name = '2'; 
```
The last two executions try to update (the third one) and insert (the fourth one) a new customer with a name that is too short according to the check constraint on the Name column, and both receive an error message.
To clean up after this exercise, close all open query windows in SQL Server Management Studio, open a new query window, and execute the SQL statements below:
```
 USE master; DROP DATABASE TestDB; 
```