THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Your TRY block may fail, and your CATCH block may be bypassed.

Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one is bypassed. Let me provide some examples. I do not intend to provide a comprehensive list of all such cases, I only want to demonstrate that sometimes CATCH blocks are bypassed. Also I would like to emphasize that in almost all the cases CATCH blocks do catch errors, and the exceptions, if any, are quite rare.

 

KILL command and timeout (aka attention) both stop execution without invoking CATCH blocks.

 

As a result of a KILL or an attention, the execution stops immediately. You can run the following script, cancel the query or kill it from another tab, and see for yourself:

 

SELECT @@SPID;

GO

BEGIN TRY

  PRINT 'Before WAITFOR';

  WAITFOR DELAY '00:35:00';

  PRINT 'After WAITFOR';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

 

You can also invoke it from ADO.Net, and you can also decrease CommandTimeout, so that the timeout occurs faster.

As you have seen, you can only catch such errors on the client.

Note: timeout and lock timeout are different. Lock timeout errors are caught by CATCH blocks.

 

Some compile errors cancel batch execution.

 

See for yourself:

 

BEGIN TRY

  PRINT 'Beginning TRY';

  BEGIN TRAN

  INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

-- make sure #t does not exists for your connection

  DELETE FROM #t;

  COMMIT;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

  ROLLBACK;

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

 

Beginning TRY

 

(1 row(s) affected)

Msg 208, Level 16, State 0, Line 6

Invalid object name '#t'.

Next batch

 

This problem is more likely to happen if you work with temporary tables.  However, because at the time of this writing we have deferred name resolution, you can get this problem with a permanent object too. Surprisingly enough, if you wrap this batch in a stored procedure, and invoke your procedure from another TRY block, the second TRY block does catch the error. You can create the procedure and see for yourself:

 

CREATE PROCEDURE dbo.TestProc

AS

BEGIN TRY

  PRINT 'Beginning TRY';

  DELETE FROM #t;

  --INSERT data.SomeData(j) SELECT 1;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

BEGIN TRY

  PRINT 'Beginning TRY';

  EXEC dbo.TestProc;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

 

In my opinion it would be preferable not to start executing a batch if there are compilation errors, and in most cases exactly this is happening:

 

BEGIN TRY

  PRINT 'Beginning TRY';

  DECLARE @i INT;

  INSERT data.SomeData(j) SELECT 1;

  PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

  SELECT 'Beginning CATCH block';

  SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

Msg 207, Level 16, State 1, Line 4

Invalid column name 'j'.

Next batch

 

If you are still reading this, also make sure you have read Erland Sommarskog’s articles on error handling:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

 and STRICT_CHECKS:

http://www.sommarskog.se/strict_checks.html

 

 This post continues my series on defensive database programming. My next post:

Without ORDER BY, there is no default sort order.
Published Wednesday, May 13, 2009 9:11 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Sasha

You write

/*

Surprisingly enough, if you wrap this batch in a stored procedure, and invoke your procedure from another TRY block, the second TRY block does catch the error. You can create the procedure and see for yourself

*/

Do you mean that we get the error from within TRY block of stored procedure? or from calling TRY block?

May 14, 2009 1:18 AM
 

Alexander Kuznetsov said:

Uri,

The error will originate in the TRY block of the stored procedure, bypass the CATCH block in the stored procedure, but it will be caught by the CATCH block in the calling batch.

May 14, 2009 8:55 AM
 

Dan Guzman said:

You brink up a good point that a CATCH block may being skipped.  Other common attention event scenarios include a query cancel or command timeout.  Not only does the CATCH block not get executed in those cases, the transaction will remain open and uncommitted unless SET XACT_ABORT is on.

May 14, 2009 12:35 PM
 

Denis Gobo said:

Alex,

what about this?

BEGIN TRAN

BEGIN TRY

 PRINT 'Beginning TRY';

   INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

-- make sure #t does not exists for your connection

 DELETE FROM #t;

 PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

 SELECT 'Beginning CATCH block';

 SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

IF XACT_STATE() =0

   BEGIN

    COMMIT TRAN

    print 'commit'

   END

   ELSE

   BEGIN

    ROLLBACK TRAN

print 'rollback'

   END

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

--------------------------------------------

result

Beginning TRY

rollback

At the end of the same batch

Next batch

May 14, 2009 1:26 PM
 

Alexander Kuznetsov said:

Denis,

When I ran your script, my output was different:

Beginning TRY

(1 row(s) affected)

Msg 208, Level 16, State 0, Line 11

Invalid object name '#t'.

Next batch

Can you elaborate - what were your settings/version?

May 14, 2009 2:37 PM
 

Denis Gobo said:

oops, never mind, I ran it twice  and that is when you get my output

I get the same as you the first time around

May 14, 2009 2:42 PM
 

Florian Reischl said:

Nice article! Thanks!

May 22, 2009 2:30 PM
 

SQL SERVER 2005- Proc Writer said:

Catch will not be executed for the compilation errors like table does not exist etc. Assume this as a bug in sql server 2005. Timeout expired error will not be caught by the catch block.

September 28, 2009 7:39 AM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

October 9, 2009 5:04 PM
 

Giuseppe Zagarrio said:

Hi, I noticed another strange behaviour on SQL Server 2008 R2. I get the same output as yours running the first time the code you posted:

--------------------------------------

BEGIN TRY

 PRINT 'Beginning TRY';

 BEGIN TRAN

 INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

-- make sure #t does not exists for your connection

 DELETE FROM #t;

 COMMIT;

 PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

 SELECT 'Beginning CATCH block';

 SELECT ERROR_NUMBER(), ERROR_MESSAGE();

 ROLLBACK;

END CATCH

PRINT 'At the end of the same batch';

GO

PRINT 'Next batch';

--------------------------------------

But if I run it again it works correctly!!! It seems like the engine caches it in the correct way.

Someone can explain it?

Thank you, nice article

October 5, 2011 6:25 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement