Error Handling in SQL 2005 and Later

An SQL text by Erland Sommarskog, SQL Server MVP.

Introduction

So this is a shame really. Way back in April 2003 I published two articles on error handling in SQL 2000, and you can still find people posting links to these articles. And, of course, there are still people out there using SQL 2000, and if you are one of them, these two articles should be useful to you:

However, in SQL 2005, the tables turned with the introduction of TRY-CATCH, and a lot of what I say particularly in the Implementing...article is simply not applicable to SQL 2005. There is little reason to implement error handling in that cumbersome way on SQL 2005 and later versions, but in possibly a few exceptional situations.

Every once in a while I have received mail from people who have asked whether I will produce a new article suited for SQL 2005. And the answer is, believe or not, yes. But SQL 2005 has been out for four years now, so it's quite a shame that I don't have anything new out. Partly, I can blame this that I had to revise all my articles for SQL 2005, and I decided to save error handling until last, because this is the most difficult topic to write about. While TRY-CATCH has made life easier, there are still plenty of quirks and gotchas when you scratch the surface.

Anyway, a couple of months back I started to write a new article, but then a bunch of other things came in between. But to give you something, below is what is intended to be the first section of the new article. It gives you a simple framework for error handling, sufficient for 80-90% of your code, without getting into much details. If you want to know some of the dirty details, the Background article is still largely applicable, even if the perspective of the article is SQL 2000. But if you are on SQL 2005, sleep over Implementing...

As for when the full article on error handling in SQL 2005 will be out, I really hoped it would be in 2010 when I first published this makeshift version. We're now halfway into 2011...

Jumpstart Error Handling

Before we go into anything else, permit me a short discussion on why we have error handling at all. Essentially, errors can be divided into two kinds: unanticipated and anticipated. An unanticipated error is due to something you had not foreseen. When you run into an unanticipated error, you need to return to a point where you have control over the situation and in many cases this means that you have to terminate the execution. If you don't, you run the risk of returning incorrect results, or even worse persist incorrect or inconsistent data in the database. Furthermore, you need to make it loud and clear that you failed to fulfil your contract; never can you permit an unexpected error to be dropped on the floor.

An anticipated error is when you do something well aware of that this can result in a certain error. In this case, you typically have some sort of an alternate strategy. In this jumpstart section, I'm only covering the basics, how to handle unanticipated errors. Once you have to learnt to master the basic mechanisms, you might be able to work out on your own how to implement strategies for anticipated errors. If not, I will cover this in a later section in this article.

Once with our philosophy spelled out, let's look TRY-CATCH which is the main vehicle for error handling in SQL Server. The structure is as follows:

BEGIN TRY
   <regular code>
END TRY
BEGIN CATCH
   <error handling>
END CATCH

If any error occurs in regular code, execution is transferred to the CATCH block, and the error handling code is executed. The original error is not propagated further, so unless you re-raise the error, the caller, be that another stored procedure or a client, will not know about the error. There is no explicit RERAISE statement, but you can easily use the RAISERROR statement, as shown in the example below.

TRY-CATCH can nest. That is in the example above, both regular code and error handling can include an inner TRY-CATCH block. Furthermore TRY-CATCH can catch errors that occur in inner scopes. Say that procedure A has a TRY-CATCH block, and within the TRY block, A calls procedure B which in its turn calls procedure C that inserts a row into a table and this fires a trigger. For some reason there is no TRY-CATCH in the inner procedures or in the trigger. If an error occurs in the trigger, execution will be transferred to the CATCH block in procedure A. More generally, if there is an error, SQL Server unwinds the call stack until it finds a CATCH block, and if there is no active TRY-CATCH on the stack, the error is passed to the client.

All and all, this is very reminiscent of the exception mechanisms found in modern programming languages such as C# and many others. The one thing that makes T-SQL different from many other languages is that when there is no exception handler and an error occurs, the execution may very well continue despite the error, whereas a program written in a traditional language will produce an error message and terminate. This makes it crucial to have your entire code guarded by a TRY-CATCH block, to avoid that an unhandled error leads to incorrect results or updates.

This leads to this general pattern for error handling, demonstrated by this example. The number to the right refers to the annotations that follows.

CREATE PROCEDURE some_sp AS                                 
BEGIN TRY                                                         -- 1
   SET NOCOUNT ON                                                 -- 2
   SET XACT_ABORT ON                                              -- 3

   DECLARE @somevar int                                           -- 4
   SELECT @somevar = 0
   SELECT 1/@somevar
END TRY                                                           -- 5
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION                        -- 6
   EXEC error_handler_sp                                          -- 7
   RETURN 55555                                                   -- 8
END CATCH                                                         -- 9
go
------------------------------------------------------------------------------------
CREATE PROCEDURE error_handler_sp AS
 
DECLARE @errmsg   nvarchar(2048),
        @severity tinyint,
        @state    tinyint,
        @errno    int,
        @proc     sysname,
        @lineno   int
           
SELECT @errmsg = error_message(), @severity = error_severity(),   -- 10
       @state  = error_state(), @errno = error_number(),
       @proc   = error_procedure(), @lineno = error_line()
       
IF @errmsg NOT LIKE '***%'                                        -- 11  
BEGIN 
   SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                    ', ' + ltrim(str(@lineno)) + '. Errno ' + 
                    ltrim(str(@errno)) + ': ' + @errmsg
   RAISERROR(@errmsg, @severity, @state)
END
ELSE
   RAISERROR(@errmsg, @severity, @state)
go
-------------------------------------------------------------------------------
CREATE PROCEDURE outer_sp AS                                      -- 12
BEGIN TRY
   SET NOCOUNT ON
   SET XACT_ABORT ON

   EXEC some_sp
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH
go
-------------------------------------------------------------------------------
-- Testing it out.
EXEC some_sp
go
EXEC outer_sp
go
-- Cleanup
DROP PROCEDURE some_sp, error_handler_sp, outer_sp               
  1. BEGIN TRY. As you see, BEGIN TRY is the first thing in the procedure. There is not really any technical reason to have it later – possibly aesthetic.
  2. SET NOCOUNT ON. This statement has nothing to do with error handling to do per se. What it does is that it prevents SQL Server from sending row counts from INSERT, UPDATE, DELETE and MERGE statements to the client. There are situations where you want these rowcounts, but more often they can confuse an improperly written client, and they produce extra network traffic. Therefore it is best practice to always include SET NOCOUNT ON, as long as you don't know that you need the row counts in the client.
  3. SET XACT_ABORT ON. I will return to the full effect of this statement in the complete article. For now, I only tell you that you should include this statement in your stored procedures to make sure that any user-defined transaction is rolled back, if the client cancels the batch. The two prime example of cancellations are query timeouts and when you press the red button in SQL Server Management Studio.
  4. Regular code. These three lines are just a token example of the actual code you would have in a procedure. You might think that you could have variable declarations outside TRY-CATCH, because they are just declarations, and not executable statements. And, yes, this is true for SQL 2005, but in SQL 2008 you can initiate your variables as you declare them, for instance DECLARE @somevar int = 0.
  5. END TRY BEGIN CATCH. Here ends the regular code of the procedure, and any COMMIT, RETURN etc that you have in your procedure should come before END TRY.
  6. IF @@trancount > 0 ROLLBACK TRANSACTION. The first thing you should do in your error handler is to check for open  transactions and roll them back. Always. No exceptions. Even if you don't start any transaction in your procedure, you may call an inner procedure that does, and you cannot trust this procedure to roll back any transaction it may have started. Now, you may argue that if the caller has started a transaction that you should not roll it back. I will discuss this in the full article, but for now I only say: doing this will only serve to make your code complex and introduce the risk for bugs.
  7. EXEC error_handler_sp. A call to the general error-handling procedure included in the script, and commented in points 10-12 below.
  8. RETURN 55555. Strictly speaking, this statement is not necessary if all error handling is implemented properly. But if your system includes legacy code that performs error checking by looking at @@error or return values, it is a safety precaution to make sure that your procedure returns a non-zero value if no called caught the error raised in error_handler_sp.
  9. END CATCH. This is always the last statement of your procedure.
  10. Capturing error information. We are now in error_handler_sp, and the idea is that every CATCH handler in the system should call this procedure. At this point we capture the error information from a couple of system functions for error-information.
  11. Re-raising the error message. The purpose of error_handler_sp is simple: to re-raise the error message. Since there is no RERAISE statement, we need to fake it with RAISERROR. To not lose any diagnostics, we tack on information we cannot pass to RAISERROR directly. If the there is no procedure name, we assume that the error occurred in a block of dynamic SQL somewhere. By adding the initial ***, we can tell if the error message is an original error message, or if it is a second-level re-raise, in which case we should just re-raise the message as is.
  12. Another test procedure which calls the first one. You can see that this procedure follows the same pattern as the first.

If you run the two test procedures you will get this output:

Msg 50000, Level 16, State 1, Procedure error_handler_sp, Line 20
*** [some_sp], 8. Errno 8134: Divide by zero error encountered.
Msg 50000, Level 16, State 1, Procedure error_handler_sp, Line 23
*** [some_sp], 8. Errno 8134: Divide by zero error encountered.

You can see that the error message is the same in both cases. The only difference is the line number; in the latter case, the error gets re-raised a second time.

As for the layout of the error message when you re-raise, there are lots of possibilities. You may want to design the message so it's easy for a consumer to split up the message in components in a reliable way. Hey, you could even make it an XML string!

One more thing, before I close the jumpstart section. Change the error line in some_sp to read:

SELECT * FROM nosuchtable

Run the two procedures again. The output is now different:

Msg 208, Level 16, State 1, Procedure some_sp, Line 8
Invalid object name 'nosuchtable'.
Msg 50000, Level 16, State 1, Procedure error_handler_sp, Line 20
*** [some_sp], 8. Errno 208: Invalid object name 'nosuchtable'.

When we call some_sp, the error is caught and re-raised as before, but the error message from the call to some_sp looks mysterious. Where are our asterisks? The answer is simple: this is a compilation error, and you cannot catch compilation errors in the scope they occur – even if they would not be raised until run-time because of deferred name resolution. You can only catch them in outer scopes. And keep in mind that this deferred name resolution is quite an ugly baby. About any statement that refers to a temp table is subject to it.

Revision History

2009-11-29 – Published a short version with the section Jumpstart Error Handling.

Back to my home page.