Error Handling in T-SQL

One of the new features available to you in SQL Server 2005 is the ability

to easily handle errors that occur in T-SQL code. In earlier versions of

SQL Server, T-SQL visibly lacked well-structured error handling comparable

to the approaches in languages such as Visual C++ or C#. The information

available about individual errors was fairly limited. As the complexity and

length of T-SQL code increased with time, the lack of easy-to-use error handling

was a significant deficiency in T-SQL that needed to be corrected.

 

Handling Errors with T-SQL

There are two broad approaches to working with errors in T-SQL. Using the

TRY...CATCH construct that I describe in the next section is something new

in SQL Server 2005. You can use the TRY...CATCH construct with error functions,

but you can also use it with the @@Error function that was also supported

in SQL Server 2000. I talk more about error functions in the "Using

Error Functions" section, later in this article.

 

If you need to write code that runs on SQL Server 2005 and also on (for example)

SQL Server 2000, you need to use the @@Error function that provides

less information than the error functions introduced in SQL Server 2005. You

also cannot use the TRY...CATCH construct in code intended to run on SQL

Server 2000.

 

The TRY...CATCH Construct

The TRY...CATCH construct is the pivot of error handling in T-SQL code in SQL

Server 2005. The TRY...CATCH construct takes the following general form:

BEGIN TRY

-- T-SQL code goes here.

-- If an error is raised execution switches to the CATCH

block

END TRY

BEGIN CATCH

-- Other T-SQL code can go here which is executed

-- when there is an error in the code between BEGIN TRY

-- and END TRY

END CATCH

Inside the TRY block or inside the CATCH block you can have any T-SQL statement

or block of T-SQL statements that you want. You can nest TRY...CATCH

constructs.

 

If no error occurs during execution of the T-SQL code between BEGIN TRY

and END TRY, then the code in the CATCH block doesn't execute. After all the

code in the TRY block is done executing, control passes to the code that follows

the END CATCH line.

 

Rules for the TRY...CATCH construct

To use the TRY...CATCH construct correctly, you must follow these rules:

 There must be no code between the END TRY line and the BEGIN

CATCH line. A syntax error occurs if you attempt to put any T-SQL code

between those lines.

 

 A TRY...CATCH construct cannot span multiple batches.

 

 A TRY...CATCH construct cannot span multiple blocks of T-SQL code.

Error message severity levels

 

In SQL Server, each error message has a severity level associated with it. The

TRY...CATCH construct in SQL Server 2005 catches errors where the severity

level is greater than 10. However, the TRY...CATCH construct doesn't

catch errors with severity levels that exceed 10 if the error results in a loss of

the database connection.

 

You can find a full list of error severity levels in SQL Server Books Online in

the Database Engine Error Severity topic.

 

Using Error Functions

Several error functions in SQL Server 2005 provide information that you can

use inside the CATCH statement.

The error functions available in T-SQL in SQL Server 2005 are the following:

 ERROR_LINE: Gives the line at which the error appears to have occurred.

The line given by the ERROR_LINE function may not be the line that

actually contains the error. You may need to examine the control flow —

for example, of nested script objects — to see exactly where the error

occurs.

 

 ERROR_MESSAGE: Gives a brief message indicating the nature of the error.

Many SQL Server error messages include parameters that provide

information about the specific context in which an error arose. The

ERROR_MESSAGE function substitutes values relevant to the context for

those parameters and returns the substituted message. You can access

error messages from the text column of the sys.messages catalog.

 

 ERROR_NUMBER: Gives the number of the error. If the error occurs in a

stored procedure or trigger, it returns the line number in the routine.

The ERROR_NUMBER function returns the number of an error when it is

executed inside the CATCH block. If you attempt to use the ERROR_NUMBER

function outside a CATCH block, it returns NULL.

 

 ERROR_PROCEDURE: Returns NULL if the error doesn't occur in a stored

procedure. When the error occurs in a stored procedure or trigger, this

function returns the name of the stored procedure or trigger.

 

 ERROR_SEVERITY: This returns the severity of a SQL Server error.

 

 ERROR_STATE: Returns an int value. Returns NULL if called outside a

CATCH block. It provides information complementary to ERROR_NUMBER.

The ERROR_STATE function provides additional information to help

you understand the information returned by the ERROR_NUMBER

function. Some error numbers can have different values returned by

ERROR_STATE in different situations. When trying to understand and

process errors, you may need to know both pieces of information —

for example, to effectively use the Microsoft knowledge base.

 

The sys.messages catalog contains a complete list of error numbers and

the corresponding messages. To view all errors and their corresponding messages,

run this code:

SELECT * FROM sys.messages

ORDER BY message_id

 

Notice that the severity column indicates the severity of the error. The

is_event_logged column shows whether or not the error is currently

being logged. A value of 0 (zero) indicates that a particular error is not currently

being logged.

 

To find the error number and severity for errors relating to a particular topic

when you don't know the error number but do know part of the message,

simply use code like the following:

SELECT * FROM sys.messages

WHERE text LIKE '%zero%'

 

This example finds all error messages that contain the word zero, including

error 8134.

 

Using error codes

The following code demonstrates how you can use the error functions in SQL

Server 2005 to display information about an error that occurs in the TRY

block. It uses an attempt to divide by zero to raise an error:

USE master

GO

BEGIN TRY

SELECT 1/0 -- Produces a divide by zero error.

END TRY

BEGIN CATCH

SELECT

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage,

ERROR_NUMBER() AS ErrorNumber,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState

END CATCH

 

The first result displays nothing because the attempt to divide by zero

returns nothing. In the second grid, the result returned by each error function

displays in its own named column.

 

If you want to store the values returned by the error functions, you must capture

them in variables. You can then INSERT the values of the variables in an

audit table.

 

RAISERROR

The RAISERROR function can access a user-defined message that is stored in

sys.messages or you can code an error dynamically by using RAISERROR.

When writing code with RAISERROR, the code takes the following form:

 

RAISERRROR(50100, 12, 1, 'Text of message goes here.')

 

Use the following code to add a new user-defined error to sys.messages,

and then to raise an error with that number. In the next section, I look at

using RAISERROR in nested TRY...CATCH constructs. To add a new message

to sys.messages, use this code:

 

sp_addmessage @msgnum = 50100,

@severity = 12,

@msgtext = N'This is a test error.'

 

The sp_addmessage system stored procedure is used to add a new message

to sys.messages. In this example, it takes three parameters that consist of

the number, the severity, and the message text of the new message.

You can test that the new message has been added successfully by using

this code:

SELECT * FROM sys.messages

WHERE message_id = 50100

You can then use RAISERROR inside a TRY...CATCH construct as follows:

BEGIN TRY

RAISERROR(50100, 12, 1, '')

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE() AS ErrorMessage

END CATCH

 

You remove the message from sys.messages by using the following code:

sp_dropmessage @msgnum = 50100

 

Using nested TRY...CATCH constructs

In some situations, you may want to nest TRY...CATCH constructs. The

nested TRY...CATCH construct is nested inside the CATCH block of the outer

TRY...CATCH construct.

 

The following example uses RAISERROR to demonstrate how to code nested

TRY...CATCH constructs.

 

First add two new messages to the sys.messages catalog by using the following

code. Notice that the message describes which construct it occurs in.

sp_addmessage

@msgnum = 50200,

@severity = 12,

@msgtext = N'This is in the outer TRY block.'

sp_addmessage

@msgnum = 50300,

@severity = 14,

@msgtext = N'This is in the **INNER** TRY block.'

 

Now that you have added two user-defined messages, you can use these to

see how errors in a nested TRY...CATCH construct are handled. The following

code shows a nested TRY...CATCH construct with RAISERRROR used to

raise an error in each of the constructs:

 

BEGIN TRY

RAISERROR(50200,12,1,'')

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS OuterErrorNumber,

ERROR_MESSAGE() AS OuterErrorMessage

BEGIN TRY

RAISERROR(50300, 14, 1, '')

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS InnerErrorNumber,

ERROR_MESSAGE() AS InnerErrorMessage

END CATCH -- Nested CATCH

END CATCH -- Outer CATCH

 

@@Error

The @@Error function was the main way to get information about errors in

SQL Server 2000 and earlier versions of SQL Server. If you need to write T-SQL

code to run both on SQL Server 2005 and earlier versions, you need to understand

how to use the @@Error function to capture information about errors.

The @@Error differs in several respects from the error functions described in

earlier sections. The @@Error function returns the error number only in the

next T-SQL statement after the occurrence of the error or when @@Error is

used in the first statement in a CATCH block. If the preceding T-SQL statement

executes correctly, @@ERROR returns zero.

The following code demonstrates that @@Error only contains a meaningful

value in the first line of a CATCH block:

 

BEGIN TRY

SELECT 1/0;

PRINT @@ERROR;

END TRY

BEGIN CATCH

SELECT @@ERROR

SELECT @@ERROR

IF @@ERROR = 8134

SELECT 'Divide by Zero Error found.'

ELSE

SELECT 'No divide by Zero Error found.'

END CATCH

 

Notice that the first two lines of the CATCH block are the same. Figure 10-5

shows the appearance after running the code in SQL Server Management

Studio.

 

If you comment out the two SELECT @@ERROR lines, then the first line of the

CATCH block is IF @@ERROR=8134, which is now true (because this is the

first line in the CATCH block) and the Divide by Zero Error found.

error message displays in the results grid.

 

If, instead of using @@ERROR, you use the ERROR_NUMBER function,

ERROR_NUMBER returns the same value whenever you use the function, as

when you run the following code:

 

BEGIN TRY

SELECT 1/0;

PRINT @@ERROR;

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

IF ERROR_NUMBER() = 8134

SELECT 'Divide by Zero Error found.'

ELSE

SELECT 'No divide by Zero Error found.'

END CATCH

 

If you need to use @@Error to give compatibility with earlier versions of SQL

Server, then assign the value of @@Error to a variable. You can then use the

value of that variable in a way similar to the way you can use ERROR_NUMBER.