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.


33
0
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

Description

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

BEGIN TRY 
   PRINT 'This prints'
   SELECT * FROM doesnotexist
END TRY
BEGIN CATCH
   PRINT 'This does not print'
END CATCH

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, http://connect.microsoft.com/SQLServer/feedback/details/496758/try-catch-should-capture-the-parse-errors 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 esquel@sommarskog.se 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