Home » sql server » Currently Reading:

From the Vault: TRY, CATCH, and Transactions

June 23, 2014 sql server 1 Comment

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

 

Currently there is "1 comment" on this Article:

  1. […] From the Vault: TRY, CATCH, and Transactions (Jen McCown) […]

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?

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/