From the Vault: TRY, CATCH, and Transactions

I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here.

Src: http://commons.wikimedia.org/wiki/File:Border_collie_jumping_up_to_catch_frisbee.jpgThe T-SQL TRY/CATCH structure (“new” in SQL 2005!) is, as the professionals say, da bomb. It’s also underused and under-understood, so let’s talk about the basics. (Or, you can skip to the summary at the end.)

A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block. – MSDN, “TRY…CATCH (Transact-SQL)

Nice…any error within a TRY/CATCH block stops the script in its tracks, so you can do whatever you want about the detected failure. For example:

BEGIN TRY

– Generate a divide-by-zero error, throw to CATCH!
SELECT 1/0;
SELECT ‘Hi there!';

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

At “SELECT 1/0″, we get an error. SQL won’t execute the next line, but instead jumps to “BEGIN CATCH”. No problem whatsoever.

But is TRY/CATCH an implicit transaction?

Nope!  Anything statement in the TRY/CATCH block that happened before the error is committed, just like in any “normal” script.

BEGIN TRY

INSERT INTO MyTable (col1) VALUES (‘This totally gets committed!’); — This totally gets committed

– Generate a divide-by-zero error.
SELECT 1/0;

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

This will insert that one row, hit the divide by zero error, and jump to the CATCH block.

What if the statement itself generates an error?

Just like a stand-alone statement, a statement that generates an error will not commit.

BEGIN TRY

INSERT INTO MyTable (col1)
SELECT ‘This will totally NOT be committed to the table, it’s erroring out!’)
WHERE 1 = 1/0;      — This totally does NOT get committed (divide by zero error)

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

This will NOT insert the one row, because the error happened inside the transaction…it can’t finish. The script jumps to the CATCH block.

Oh yeah? Maybe I’ll just define an explicit transaction!

Excellent, that’s fine. If that’s what you need: for everything in the TRY/CATCH block to succeed or fail together, do that. BUT, you must deal with the open transaction in the CATCH block!

BEGIN TRY

BEGIN TRAN

INSERT INTO MyTable (col1) VALUES (‘This gets committed at first…’); — This gets committed at first, in the open transaction…

– Generate a divide-by-zero error, throw to CATCH!
SELECT 1/0;

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN;  – You must explicitly deal with the transaction (e.g., like this), which is still open when we throw to CATCH!
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

This will open the transaction, insert the one row, error and throw to catch…and the transaction is still open. So we have to deal with it. In this case, I want the whole explicit transaction to roll back.

Sum it up for me (TL;DR)

No worries.

  • A TRY/CATCH block does NOT define a transaction. Each statement within it is a transaction, just like normal.
  • The normal rules apply:  a statement completes, the next statement errors, the first statement still stands (it was committed).
  • But, if you define an explicit BEGIN/COMMIT TRAN block within the TRY, you must handle it. Either commit the thing, or rollback, as your process requires it.

All of this is with the default settings. If you go messing with XACT_STATE or other wibbly wobbly, timey-wimey options, we’ll have to have yet another talk.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

 

One thought on “From the Vault: TRY, CATCH, and Transactions”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>