bug in exception handling (begin try... begin catch...)?

bug in exception handling (begin try... begin catch...)?

Post by B D Jense » Sat, 06 Oct 2007 21:06:08


begin try

-- insert ...

select * from nonexistingtableorfunction;

end try

begin catch

print 'catch me if you can'; -- line will not be printed

end catch;



begin try

exec doNotExitingprocedure;

end try

begin catch

print 'this line is printed :-)';

end catch;



Why this different behaviour?

If you wrap 1. example in anthother proc with exception handling
(begin try... begin catch...),

the wrapping proc will catch the error.



The above behaviour is not what I would expect compared with other
languages such as Java, C#, PL/SQL...

Greetings
B. D. Jensen
 
 
 

bug in exception handling (begin try... begin catch...)?

Post by TheSQLGur » Sat, 06 Oct 2007 21:57:26

I think this is one of numerous reasons I have noticed a distinct lack of
uptake on the new TRY mechanism. Until it really does catch errors I won't
use it, nor advise my clients to.

 
 
 

bug in exception handling (begin try... begin catch...)?

Post by Russell Fi » Sat, 06 Oct 2007 23:14:39

B D,

I believe that what is happening here is that in your first example, the
batch is failing with a syntax error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'nonexistingtableorfunction'.
This means that it failed at compile time and never executed. Since it
never executed, the try / catch had no chance to do anything.

In your second example, the called routine got the same error as above and
never executed.
However, in this case the calling routine is running just fine and the try /
catch sees that the called routine got an error.

At least, that is what it looks like to me.
RLF
 
 
 

bug in exception handling (begin try... begin catch...)?

Post by TGluY2hpIF » Sun, 07 Oct 2007 03:54:03

Take a look at the BOL here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

In your first case, the missing object caused the batch to terminate, and
TRY/CATCH won't help if the batch is terminated. In your second case, the
batch is not terminated and TRY/CATCH works.

Yuu can place a PRINT statement at the end of each batch to see whether the
batch is terminated before it reaches the end.

Linchi