doobya (7/7/2010)
Grant Fritchey (7/7/2010)
You're dealing with a fundamental change in behavior. If you're raising errors inside the procedure, the errors are recognized by the TRY/CATCH construct, as designed. That's how it's supposed to work. Your previous construct worked for you, but it's not functional within the new construct.Things change. You can't use *= style joins in 2008 now. Code behaviors get deprecated and if you want to move on with new versions of the software, you have to deal with it.
Yes - that is the point I was trying to make:
watch out! using try catch will change, even break, *existing* stored procedures
that isn't obvious - in fact nowhere have I seen it mentioned except in my posts
in all the other languages I use a procedure is a predefined behaviour
the only way to modify its behaviour is by changing the parameters
this means if you have a library of stored procedures - you have to rewrite all of them, in case any of them are called from within a try block
the way MS should have done it:
that existing procedures that are not using try blocks should behave the SAME
but if, upon exit, @@error <> 0 THEN catch the error in the parent try block
that would be sensible - the current approach makes no sense
Absolutely not starting a fight, but I do disagree. I think your approach to have multiple errors raised and expect to get and see multiple errors, on purpose, is the issue, not the implementation of TRY/CATCH. That's not an approach I'd take or advocate. If you're trying to communicate messages back to the calling application, there are better ways to do it rather than RAISERROR.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning