TRY-CATCH should always work! - by Erland Sommarskog

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 739957 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 4/30/2012 1:41:10 PM
Access Restriction Public


This comes up at least once a week in the forums. Someone tries this:

   PRINT 'This prints'
   SELECT * FROM doesnotexist
   PRINT 'This does not print'

That is, the error appears in the TRY block, but since this is a compilation error, you the local CATCH block does not fire. It there is a calling procedure and it has a CATCH block, that CATCH block is entered because of the error. But who says that there is a calling procedure? 

This is furthermore aggrevated if there is a BEGIN TRANSACTION in the TRY block; the transaction remains active after the procedure exits, which can have very unpleasant consquences if the programmer has not catered for this situation.

I remember that in some beta version of SQL 2005 the situation above resulted in an access violation, and maybe it was OK for in SQL 2005 when TRY-CATCH was a vast improvement over what we had before. But two releases later, no, this is not OK anymore!

It's a matter of making it possible writing reliable code: since you insist on deferred name resolution, this error is by means unlikely at run-time. This fact is alone very bad, but not being able to handle it appropriately is even worse.

It also a matter about the faith in the product. When people start to play with TRY-CATCH and then find that it doesn't work accoridng to their expectation, that is not likely to increase their faith in SQL Server - it could even be decisive factor that makes them think that SQL Server is so buggy unrelaible that they make Oracle their choice.

There is a duplicate entry for this Connect item, which you closed as Won't Fix. Please don't do that ever again! The current behaviour is bad, bad, bad.
Sign in to post a comment.
Posted by Microsoft on 4/12/2013 at 2:29 PM
Hello Erland,
I am closing this as "by design". While we don't have plans to change the deferred name resolution, we might add other features that will enforce stricter compilation requirements in the future - similar to schemabinding option or view/function.

Umachandar, SQL Programmability Team
Posted by SAinCA on 2/13/2013 at 4:23 PM
I reran the offending query on SQL2012CU5 and it now catches a floating point error, unlike SQL2005SP4, which emitted the "domain error".

Looks like Microsoft fixed this somewhere along the way to SQL2012, thankfully.

The snippet was: 864.822115788*POWER(([1830]-0.119082907501),-0.612305280089)
where [1830] is a calculated column from another, nested, aggregation, whose value may be zero.
Posted by Erland Sommarskog on 1/27/2013 at 12:22 PM
SAinCA, if you see this, can you mail me on with an example?
Posted by SAinCA on 10/18/2012 at 4:04 PM
Would you also consider how a CATCH can successfully handle POWER(<negativeNumber>,<maybeNegativeExponent>) when the function hits an imaginary number and blows the SP out of the water with a "domain error".
Posted by Microsoft on 6/7/2012 at 11:32 AM
Hello Erland,
Thanks for the feedback. We will look at how we can enable catching of binding errors for a future release.

Umachandar, SQL Programmability Team