Try and Catch the Wind - Further Adventures of YukonJack

by Bill Wunder

It was probably one of those forgone conclusions that I would somehow be disappointed with the promise of new and better error handling capabilities in Yukon. After all, it would be a colossal leap from the catch-me-if-you-can @@error we've learned to live with up through SQL Server 2000 to any sort of a legitimate - read that consistent, robust and reliable - error handling capability in T-SQL. I have to admit that my eyes glazed and I stopped listening way too soon when I first heard there would be exception handling in Yukon. I understood that to mean real, catch all the errors one could ever dream of catching and handle expediently as if you were suddenly a programming language exception handling. I ignored the fine print such as the word "transaction" being peppered throughout even the earliest blurbs. I even managed to convince myself that fatal-to-the-batch was about to become a thing of the past. All I needed to know was that Yukon boasted Try-Catch in T-SQL and I was floating in the clouds. When I finally read the Books Online pages in the beta 1 release that talk about Try-Catch my inner helium voice took a blast of smog. Then I went back and re-read Itzik Ben-Gan's T-SQL enhancements white paper found on the resource CD that came with the beta and sure enough - plain as day - he had much more accurately describe what I was to find in the beta 1 release than what I remembered reading. I had simply blanked out all the details that didn't match my delusion. Still, even after getting my eyes opened a little about what we really have with this new Try-Catch feature, I decided to give it a go and see what I could do with the new CLR-ish error handler. After some testing I'm still left with some questions and some disappointments though I remain hopeful that with beta 2 and beyond Yukon will move us to the lofty heights of error handling I had already imagined. For now at least, the balloon, even if thin and round and red, is still blown with just a little too much lead. Let me lay out a test case for you to show you what I learned. First, it may be useful to begin with a discussion of the Try-Catch rules.

The Rules

The most important rule is that errors must occur inside the Try block and must be raised at the "transaction abort" level in order handle them in the Catch Block. The Try and Catch blocks must be in the same "batch" and the Catch block must occur immediately after the Try block. The syntax then looks like:

    begin try

        {sql statement | sql block }

    end try

    begin catch tran_abort

        {sql statement | sql block}

    end catch


To effect the Catch block you have to be in a transaction. Further, to make sure all errors are raised at  the "transaction abort" level you must be running with SET XACT_ABORT ON. My tests indicate that in the case where you raise custom errors with the new TRAN_ABORT switch you do not need to have XACT_ABORT on but if you want to trap system raised errors the only option seems to be to SET XACT_ABORT ON. Furthermore, you need to use explicit Begin Tran and Commit [Tran] statements in the Try Block and Rollback [Tran] in the Catch block. Being in a transaction gives us the ability to commit and, at any time appropriate, roll back all statements in the transaction. As we know - even in pre Yukon T-SQL - without XACT_ABORT ON a rollback will not rollback all changes in a multi-statement transactional chain of statements. With the "transaction abort" requirement for Try-Catch processing the real meaning of XACT_ABORT becomes more clear and relevant. An error must be raised at the "transaction abort" level in order for the the Catch block to be able to ... well... catch it. Turns out that this is exactly what SET XACT_ABORT ON does. It raises all errors at the "transaction abort" level. Didn't you always wonder? As mentioned,  with RAISERROR there is a new switch: WITH TRAN_ABORT so presumably you can raise your own application errors and still get the benefit and use of your procedure's Try-Catch handler.

Try-Catch blocks may be nested.

Fatal to the Batch

According to the documentation:

When a transaction abort error occurs within a TRY block, an exception is thrown and the program control is transferred to the associated CATCH block. The CATCH block handles the exception and the program control is transferred to the first T-SQL statement that follows the current TRY...CATCH construct.

I understand this to say that my batch will continue to execute until I stop it. Interestingly this didn't quite turn out to be my experience. What I observed is that I could not return results or make calls to stored procedures either in the Catch block or after the Catch block if a "transaction abort" error occurred. I was even able to easily find an error condition that broke the Try-Catch processing all together and was in fact immediately fatal to the batch when it occurred. What's worse, that error also left a transaction open. Fortunately I could still do inserts, updates and deletes and I could still "print" stuff. However, the only selects that were possible once program control was passed to the Catch block were the case where I inserted the selected result back into the database. I couldn't get any selects in the batch to return results to the Workbench results pane once an error had been caught even though the line of T-SQL code was obviously processed. Very mysterious indeed.

Incredible Possibilities

I have to say that after a little testing I am impressed with Try-Catch processing. If you'd like to take a closer look at my test I've posted the script along with the output for those that don't have a copy of the beta software to try it out for themselves. I'm guessing the inability to return results once the Catch block kicks in is either a bug in my test setup or a beta 1 bug. I have to admit I'm not seeing anything I could do to get this working as I would expect it to work. I was also disappointed that I still have a situation where some errors will be fatal to the batch and some won't. In truth the most frustrating thing about that behavior is having to figure out and remember which errors fall into which category. As with select processing, I'm going to keep hoping for a fix to this one before Yukon goes gold. I'm less certain this one will change, but I wish I didn't have to be in a transaction to use this error handling method. Makes sense to me that it would be tough to get Try-Catch to work without the transaction, particularly in a simple recovery model, still I've no doubt that the minds on the SQL Development team can come up with some way to hang on to transaction log info for Try-Catch processing that doesn't have the contention issues that surround a long running transaction. That's going to be a real limiting factor for my ability and interest in Try-Catch exception handling. Seems like they are almost there with Snapshot Isolation so maybe I won't completely give up hope just yet. Guess I'll take a closer look at Snapshot Isolation and all the other cool stuff in Yukon beta 1 and give 'em a little more time on the Try-Catch error handling before I make any final decisions about where it will and won't help me. Oh yea, and I need to figure out why Set NOCOUNT ON isn't working for me...