Exception Handling in SQL Server 2000 and 2005

By : Amol Kulkarni Jul 27, 2005

Printer friendly

Error handling plays a vital role when writing stored procedures or scripts. In this article I will discuss error handling in SQL Server 2000 and SQL Server SQL Server 2005. Before starting the discussion of the error handling part, I will take you through the different components of an error message. Here is a typical error message you get when working with SQL Server 2000 Query Analyzer.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__test__08EA5793'. Cannot insert duplicate key in object 'test'.
The statement has been terminated.

The above message has some components like Msg, Level, State and Line. I will discuss each one these components in detail.

Mgs: This is the message number. Each error message has a number starting with 0. In the above example, the error (message) number is 2627, and each such message has its own message text assigned to it. You can find information on all the messages in the sysmessages table in the master database. When writing your own scripts you can also choose to have your own custom error messages by using the system stored procedure sp_addmessage. Message numbers from 50001 and up are reserved for user-defined use. Lower numbers are system defined.

Severity Level: This defines the severity of the error generated. It is a number between 0 and 25.

Any number between 0-10 defines some informational message or a warning; numbers between 11-16 are the errors in the programming (writing scripts); and a severity level 17 and above defines problems with the lack of server resources or hardware problems. Any error above 20 will terminate the connection.

State: This is a number between 0 and 127. There is not much information disclosed about this by Microsoft. Hence, I will not discuss state levels in this article.

Line: This specifies the line number in the procedure/function/trigger/batch where the error has actually occurred.

Error Handling in SQL Server 2000 & SQL Server 2005

There are two ways an error message can appear in a SQL Server 2000 or SQL Server 2005; SQL Server can call the error, or you can generate an error yourself using the command RAISERROR. Let's take a brief look at RAISERROR. Here is a sample statement:

RAISERROR('This error is raised by me', 16, 1)
Here, you supply the message text, the severity level, and the state. The output looks like this:
Server: Msg 50000, Level 16, State 1, Line 1
This error is raised by me

Instead of the message text that we have passed to RAISERROR, we can specify an error message number that we have created using the sp_addmessage stored procedure. The number must be from 13000 through 2147483647 for a user defined message. If a number is supplied which does not exist in the sysmessages table, you get a warning saying that no message text was found for the error.

Now that we know the basics of SQL Server error messages, let’s take a look at how error handling works in Transact-SQL. SQL Server performs error handling with the use of @@ERROR global variable. Whenever an error is generated the @@ERROR variable stores the appropriate error number with it, and for success it stores 0. The error number can be positive, negative, or 0. The value of @@ERROR is volatile, i.e. it changes with every execution of the statement. Let’s take a look at the following code:

When the above code is run, the following error message results:

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'a', table 'AdventureWorks.dbo.ErrorTest '; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error is 0

You can observe from the output that an error has occurred, but the @@error value is 0, a success. Here, line 3 has generated an error and the @@error value is set to some error number, but as line 4 is a success, @@error value is reset to 0, hence the output is 0. With this we can conclude that @@error values changes with every execution of statement in the code. Because the @@error values changes so quickly, you can use a local variable to store the @@error value and use it whenever needed, as shown below:

Here I have used a local variable @err to store the value of @@error. When I run the code, here is the error message:

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'a', table 'AdventureWorks.dbo.ErrorTest '; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error is 515

The other possible ways to handle errors are to use the @@rowcount and @@trancount global variables. Like the @@error, these variables are also volatile.

@@rowcount is a global variable which stores the number of rows affected in the most recently executed statement. As I said, this value is volatile; its value has to be stored in local variables to be used later. There can arise situations that you want to handle certain cases as errors where SQL Server returns a success. For instance, you write an UPDATE query and you expect that it modifies n rows, but it is not an error with respect to T-SQL if it did not modify any rows. Such situations can be handled using @@rowcount. You can raise an error using RAISERROR if the value of @@rowcount is not what you have expected.