Home » Beginner »Dev »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

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

 

Currently there are "4 comments" on this Article:

  1. 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. [...] TRY, CATCH, and Transactions - Jen McCown (Blog|Twitter) looks at the behavior of transactions when working with a TRY..CATCH Block. [...]

  3. Varsham Papikian says:

    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

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

April 14: Oklahoma City SQL UG's 5th Anniversary
April 28-30: Powershell Summit in Bellevue, WA
May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/