An SQL text by Erland Sommarskog, SQL Server MVP.
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 hope it will be in 2010.
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
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
DECLARE @somevar int = 0 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.
2009-11-29 – Published a short version with the section Jumpstart Error Handling.