TRY, CATCH, and Transactions

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

 

5 thoughts on “TRY, CATCH, and Transactions

  1. Justin Dearing

    Jen,

    Great article on a great feature. However, those of us lucky enough to be running 2012 now have the THROW keyword as an alternative to RAISERROR() which also allows you to rethrow from a catch block.

    http://msdn.microsoft.com/en-us/library/ee677615.aspx

    SQL MVP Lenni Lobel talks about it here:
    http://lennilobel.wordpress.com/2011/11/12/throwing-errors-in-sql-server-2012/
    And gave talks about it in NY and NJ when he was doing his “whats new in 2012” circuit

  2. Pingback: Something for the Weekend - SQL Server Links 28/06/13 • John Sansom

  3. Varsham Papikian

    Jen, nice summary.

    Just one comment – maybe it is also appropriate to mention that there are certain cases not handled by TRY…CATCH. I know that you are aware of them – just want to make sure that ‘new’ people reading the post don’t get the impression that they have a universal tool which will work all the time 🙂

    From MSDN:
    http://msdn.microsoft.com/en-us/library/ms175976.aspx
    >>
    Errors Unaffected by a TRY…CATCH Construct
    TRY…CATCH constructs do not trap the following conditions:
    – Warnings or informational messages that have a severity of 10 or lower.

    – Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.

    – Attentions, such as client-interrupt requests or broken client connections.

    – When the session is ended by a system administrator by using the KILL statement.

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

    – Compile errors, such as syntax errors, that prevent a batch from running.

    – Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

    These errors are returned to the level that ran the batch, stored procedure, or trigger.

    If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.
    >>
    Another aspect of it is that some beginner developers don’t realize that the application calling the code does not get ‘notified’ that an exception happened and sometimes they do need to be ‘aware’ of that too.

    But, like you said, maybe these are topics for another talk 🙂

    Thanks,
    Varsham Papikian

  4. Pingback: From the Vault: TRY, CATCH, and Transactions | The MidnightDBA Star-Times

Comments are closed.