SQL Server Reference Guide

It's ironic that one of the most frequent code errors is... not dealing with errors. No matter how trivial the process may be, the code should recover or bail out of any error that occurs. This might be due to the way we learn to code — we see examples on the web for a code construct, and to be concise the author (including myself) doesn't always put error handling in the example. But in almost every case, not matter how trivial, you should include error handling within your Transact-SQL code.

The program that asks for data from the database will often have error handling code — often called "exception handling" - in it, but that might not cover the specifics of what SQL Server can provide back to the application, or it may not know what to do with the error codes when it does.

Of course you want to handle the errors, not just find them. Error handling isn't used only to catch any errors your program might have, because most programs won't work at all if the code is wrong. By "handle" I mean that you try to anticipate things that might not work as expected, and then either take corrective actions or at least somehow notify the user. In addition, it's my preference to incorporate a logging function in most all code. Error handling code can also use this logging mechanism for proper debugging or historical problem-solving later.

In the versions prior to SQL Server 2005, T-SQL code didn't have a robust error-handling mechanism. That changes to a more robust mechanism starting in SQL 2005. But even in the earlier versions, there are some primitive functions to help track down and handle many error conditions.

Using the "pubs" sample database, you can generate an error with this code:

 /* Error Generation in the pubs database */
USE pubs
GO
INSERT INTO authors 
VALUES ('172-32-1176'
, 'White'
, 'Johnson'
, '            408 496-7223            408 496-7223            '
, '10932 Bigge Rd.'
, 'Menlo Park'
, 'CA'
, '94025'
, 1) 

If you run this code, here's the message you should get:

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

I'll explain a bit more about the parts of the message you're seeing here in a moment, but it's also important to understand that this is a completely separate thing returned by SQL Server. In other words, whenever you run a query, you normally expect to see results. But there in the query window you'll notice that you have at least two panels — one for the result, and the other for the messages from SQL Server. Often this is only the number of rows returned and a success code that is often suppressed, but here you can see that it has a larger payload. Developers can receive results from SQL Server, but they also have programmatic access to this message as a separate returned set. In that manner they can deal with not only success events but also "exceptions" or errors. Because of that, the parts of this message are very important.

First, you'll receive a message number. This number is actually stored in a table in the "master" database, called SYSMESSAGES. If you run a query on that table, you'll see that the message number along with the message text (at the end of the error statement) is found inside.

Next is the level of the error. Anything lower than 10 is a warning or information error. If the error is in the 11-16 range, then the error is due to a T-SQL error — which is exactly what we see here. Errors from 17 to 20 are SQL Server internal or hardware errors, and anything higher than 20 causes the system to abort the connection.

The State of the error is largely important only to command line tools, such as SQLCMD or BCP. With those tools, you can set the ERRORLEVEL variable a command interpreter (such as the cmd.exe process in the Windows operating system) expects. The range of this code is from 0 to 127.

If the error is generated in a stored procedure, you get another bit of information, with the name of the procedure that generated it.

The Line code tells you which line number in the code responded with the error. This is a source of constant frustration for some developers and data professionals, since this is where the error was returned, not necessarily where it actually failed. This isn't due to a bug; it's just that the lines that come before another statement might cause the ones following to be in error, even if the syntax is accurate in both statements. In that case, the parser can't really figure out where the error is, and places you close to the line. This happens a lot with misplaced commas. You'll notice that I move commas to the first line in my code so that I can ensure they exist in the right place, and in the right order.

In any case, double-clicking the red error in Query Analyzer (SQL 2K) or Management Studio (SQL 2K5 and higher) will take you directly to the line that SQL Server thinks caused the error.

Next I'll explain how you can deal with errors by using these parts of the return codes — and how you can even create your own error messages and cause them based on conditions you set.

Using @@ERROR

SQL Server tracks the error status of the code just like the transaction count we've seen in previous articles. You can see this error count by trying to insert a record that violates the primary key in a table, once again in pubs:

 /* Trap for a specific error and create a printout */
USE pubs
GO
INSERT INTO authors 
VALUES ('172-32-1176'
, 'White'
, 'Johnson'
, '            408 496-7223      '
, '10932 Bigge Rd.'
, 'Menlo Park'
, 'CA'
, '94025'
, 1)
IF @@ERROR = 2627 
 PRINT 'Primary key error!' 

The first part of this script is an insert statement. Things start to get interesting around the last two lines. The script causes a printout when error code equals 2627 to print a message. While this "traps" the error, it doesn't handle it in any way.

To handle the error properly, the "IF" statement needs to be followed by logic that actually does something about the condition that was raised. To be fair, there isn't a lot code can do about a primary key insert, but you could allow the calling program to help. In the tutorial on stored procedure parameters, you'll learn more about using a RETURN statement to pass the error code back to the calling program.

Another common use of this construct is in a more generic way to make changes in the database. You can use the BEGIN TRANSACTION logic I explained in the last tutorial to back out a transaction that causes an issue:

BEGIN TRANSACTION
INSERT INTO authors 
VALUES ('172-32-1176'
, 'White'
, 'Johnson'
, '            408 496-7223      '
, '10932 Bigge Rd.'
, 'Menlo Park'
, 'CA'
, '94025'
, 1)
IF @@ERROR <> 0 
 ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION 

This time, the code sets the beginning of a transaction in the first line. It then tests for errors; if the error code goes higher than "none" (0), it rolls back the transaction, and nothing happens. If there are no errors, the code commits the transaction.

TRY...CATCH

Beginning in SQL Server 2005, a new error-handling construct was added. It's called a "TRY...CATCH block" and is similar to the error handling found in Visual Basic.NET and C-Sharp. It's more powerful than just trapping the error using @@ERROR.

To use the TRY...CATCH blocks, you place the condition to want to test for in the "TRY" and then you place any code you want to happen on an error condition in the "CATCH." You need to follow a TRY block with a CATCH block. Here's the Syntax layout:

 BEGIN TRY
  Some T-SQL Statement
END TRY
BEGIN CATCH
  Statements you want to run if there is a problem
END CATCH 

You can also nest TRY...CATCH blocks, giving you a method to implement complex error-checking. Within the new T-SQL language you also have a lot of information you can find out about the error. Here's a sample that not only catches the error, but displays a lot of information about the error:

 BEGIN TRY
  SELECT * 
  FROM BadTableName
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS 'ErrorNumber'
, ERROR_SEVERITY() AS 'ErrorSeverity'
, ERROR_STATE()) AS 'ErrorState'
, ERROR_PROCEDURE() AS 'ErrorProcedure'
, ERROR_LINE()) AS 'ErrorLine'
, ERROR_MESSAGE() AS 'ErrorMessage'
END CATCH 

This generic CATCH block doesn't handle the error, it only prints out information about it. In this case, the error deals with a non-existent table, which your code can't do lot about. All you can do is return as much information as possible to the calling program so that the developer can deal with it.

That's just the bare minimum, however. You should always think about the types of errors that a particular call might generate, and do more with it. For instance, you can check the error number for 1205 — the error number of a deadlock. In the case of a deadlock, two transactions depend on each other's changes to commit. You can set a delay (using the WAITFOR) statement in the CATCH block and attempt the change again after a certain period of time. That will allow the other transaction to complete, and prevent a subsequent deadlock. Of course, you need to think the logic through so that you don't do something harmful, such as deleting a previous insert (unless that was your intention). The point is that you can deal with the error automatically rather than just returning error information.

Using RAISERROR

The system can raise errors when it encounters a problem, but you can also raise your own errors. The T-SQL command "RAISERROR" returns an error code of our choosing, along with a standard or custom level and message. Your code can then check for these levels and messages and handle them accordingly.

Looking in the sysmessages table in the master database, you'll find that SQL-Server error messages range from 1 through around 21,000. Microsoft reserves the numbers 0-50,000 for itself, and you can set error conditions of your own with a higher number.

Here's a sample of a statement that returns a custom error of level 16, a state of 1, and a custom message text:

 /* Calls an error */
RAISERROR ('Custom error - check code', 16, 1) 

Using this statement in code, combined with an IF statement, allows the flexibility to give more information to the user or to the handling program, so that the developer can deal with it properly.

To extend this command, you can add messages to the sysmessages table with a special stored procedure:

 /* Create a custom message */
USE master
EXEC sp_addmessage 60000
, 16
, 'This is my special message.'
GO 

This code adds message number 60,000 and a level of 16, with the text shown above. You can call that message like this:

 /* Calls a custom error */
RAISERROR (60000, 16, 1) 

Make sure you include code within all your programs to deal with errors that may occur. At the very least you should have error handling at the general program level, but you should also trap specific errors that you think might occur. During your unit tests and other program testing, make sure you examine any errors you have to ensure you put them into your error handling — even if you don't think they will occur again.