July 7, 2010 at 5:38 pm
doobya (7/7/2010)
bteraberry (7/7/2010)If you want 'Y' to happen regardless of what happens with 'X' then don't put it in the same TRY.
that is what I *am* doing! X and Y are in a SP with NO TRY BLOCK
but the CALLING procedure IS using a TRY block and therefore breaks the tested and working logic
Again, take a deep breath. You're not thinking. Everyone is telling you the same thing and you're not listening. If a higher severity error is unhanded in spA and spA gets called from within a TRY, it will stop execution and go to the CATCH. This is supposed to be this way and any other way would be stupid.
what MS has done is a complete mess, I have described the correct approach that they should have taken
which would allow procedures both with and without TRY blocks to work together perfectly, which I will explain again:
for code in a procedure that is outside of a LOCAL TRY block
that procedure *should* behave as it was written - no modification to existing behaviour
whether or not the CALLING procedure is using a LOCAL TRY block or not
IF the calling procedure IS using a LOCAL TRY block and calls a procedure which returns with @@error <> 0
(and severity level > 10) THEN control should be passed to the CATCH block
that is the correct, predictable and reliable way to implement it
I doubt Microsoft would have made such a terrible mistake without reason - maybe there is some insurmountable
hurdle to implementing it correctly - and they had no choice - who knows
I'm sorry, but what you are suggesting is stupid. Crippling the functionality of error handling so that people can make calls to ghetto code without errors being handled is absurd. The simple solution is that if you don't want your errors to be handled in a particular call, do not make that call within the context of a TRY...CATCH.
the only safe approach is to rewrite ALL existing stored procedures to work whether called within a TRY block or not ...
I agree with you on this point. I would highly suggest making an effort to update your code as error handling is just one of many major improvements. While you're in the process, just don't make calls to yet-to-be-updated procs within TRY...CATCH blocks.
Is that common knowledge or not?
I think most people know and accept that after a 'BEGIN TRY' and before and 'END TRY', if an error is encountered the result will be the CATCH. That's the whole idea of error handling.
If it is common knowledge please supply a link to an explanation and I apologise
Otherwise please thank me for pointing it out
But please don't miss the point
Look up TRY...CATCH in Books Online. What you've discovered is exactly its purpose.
July 7, 2010 at 5:54 pm
Gosh... just adding my 2 cents... I don't understand what all the controversy is. The original post returns the results exactly the way I'd expect such code to return for the way it's written. Running proc A directly produces and error where expected and continues running... as expected. The TRY in proc B shortcircuits proc A as soon as the appropriate level error in proc A occurs.
And, yes... the use of TRY/CATCH [font="Arial Black"]DOES[/font] change the functionality of the code insofar as the operation of errors with a severity of 11 or greater... the code is operating as designed and documented in Books Online.
Now, if you really want to have some fun, throw a transaction into proc A with SET XACT_ABORT ON. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2010 at 5:56 pm
Again, take a deep breath. You're not thinking. Everyone is telling you the same thing and you're not listening. If a higher severity error is unhanded in spA and spA gets called from within a TRY, it will stop execution and go to the CATCH. This is supposed to be this way and any other way would be stupid
it isn't stupid ... because any procedure written without a TRY block will already have @@error style resumptive error handling
*or* will work incorrectly if called without a TRY in the call stack
Look up TRY...CATCH in Books Online. What you've discovered is exactly its purpose.
I have read it in detail ... it makes no mention of how this situation is handled - which is why I wrote tests to find out
The end result is the same - there are two ways to call the same procedure and they result in different behaviour
which means that a stored procedure isn't really a procedure (a reusable behaviour)
but is acting more like an include file
with "exec dbo.spBlah" equating to "#include <dbo.spBlah>" but with parameters
Don't get me wrong - I am not arguing "against" TRY CATCH
Just illustrating that there is subtle problem which nobody seems to realise except me
If this is common knowledge - prove it with a link
I have searched this forum and google and found no mention of the dual behaviour issue
July 7, 2010 at 6:02 pm
doobya (7/7/2010)
If this is common knowledge - prove it with a link
I have searched this forum and google and found no mention of the dual behaviour issue
Common knowledge that an error handling mechanism handles errors? That's too obvious to possibly be true.
Good luck with stuff.
July 7, 2010 at 6:45 pm
doobya (7/7/2010)
Grant Fritchey (7/7/2010)
But if you have pre-existing procs with established and functioning logic, why are you changing them to use TRY/CATCH at all? I would only suggest rewriting them as needed and migrating them to TRY/CATCH, and yes, updating the logic then.TRY/CATCH does work differently than @@error and thank the gods that it does. For example, please show me how to catch a deadlock error and resubmit the query without using TRY/CATCH.
Why are people struggling to understand this simple post?
- I have an EXISTING stored procedure that WORKS FINE "dbo.spWorks"
- if I call this stored procedure "dbo.spWorks" from OUTSIDE a TRY block - it works OK
- if I call this stored procedure "dbo.spWorks" from INSIDE a TRY block - it does not work any more
I am NOT changing anything - just CALLING the SAME, UNCHANGED procedure from both INSIDE and OUTSIDE TRY blocks
Excuse the caps but I am getting frustrated when people are missing such a simple point
The trouble is that you are not understanding what people are saying - I think not understanding it at all. A stored procedure behaves in an environment, when you change the environment the behaviour changes. One part of the environment is the parameter values - I think you would be somewhat horrified if its behaviour didn't change when you changed the parameters. Another part of its environment is the schema it references: change that, and you will change the behavious - in fact the SP may go from doing something useful to falling about all over the place. Yet another part of its environment is the transaction isolation level - changing that can change the stored procedure from never causing a deadlock to causing frequent deadlocks. I could carry on listing components of the environment for a very long time, and all of them potentially change the behaviour of the stored procedure. but I think I've listed enough now. You can either accept that whether the stored procedure is invoked within a try block or not is one of those environmental factors that determine its behaviour, or you can refuse to recognise that simple fact - but if you continue to adopt the latter course you will never be able to cope with SQL or with any other programming language that has serious exception handling.
Tom
July 8, 2010 at 2:07 am
Excuse the caps but I am getting frustrated when people are missing such a simple point
No one is missing the point. TRY...CATCH does exactly what you describe, it catches the first error no matter how deeply nested it is and stops executing code that follows, it is not a bug, it works that way by design, it always has.
And yes it can cause you great problems if not used carefully.
as an example, in csharp, calling a method from within a try block or not does not change the called method
because that would be insane!
But by your definition, it would. It would change it's 'behaviour' as it would stop executing code at the first error.
Far away is close at hand in the images of elsewhere.
Anon.
July 8, 2010 at 3:46 am
Thanks all for your comments
as long as the point is clear - mixing TRY with @@error can cause subtle problems
and this point is not made in BOL, breaking changes, etc.
and BOL is ambiguous about where in a resumptive procedure the execution will stop
as throwing the error upon *returning* from a resumptive procedure would allow existing code to behave as expected
I was surprised to find a different behaviour
July 8, 2010 at 6:17 am
doobya (7/8/2010)
Thanks all for your commentsas long as the point is clear - mixing TRY with @@error can cause subtle problems
and this point is not made in BOL, breaking changes, etc.
and BOL is ambiguous about where in a resumptive procedure the execution will stop
as throwing the error upon *returning* from a resumptive procedure would allow existing code to behave as expected
I was surprised to find a different behaviour
If you don't mind, I'll just go ahead and disagree with you on all those points. 😉 It all works exactly as I expect it to work according to BOL and I find no ambiguity in any of it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2010 at 8:11 am
If you don't mind, I'll just go ahead and disagree with you on all those points. 😉 It all works exactly as I expect it to work according to BOL and I find no ambiguity in any of it.
dftt
July 8, 2010 at 10:24 am
doobya (7/8/2010)
If you don't mind, I'll just go ahead and disagree with you on all those points. 😉 It all works exactly as I expect it to work according to BOL and I find no ambiguity in any of it.
dftt
Two things.
First, Jeff isn't a troll. He is one of the most knowledgeable people on SSC and someone I consider a friend and mentor.
Second, if a stores procedure were executed as an autonomous unit of code within a TRY/CATCH block, you would nees to manually code the testing of the return value from the stored procedure in order to raise rhe appropriate error in order for the TRY/CATCH to work properly. In addition, the TRY/CATCH would have issues with unexpected errors that were not caught inside the stored procedure.
July 9, 2010 at 4:58 am
Doobya
With no offence, let me put my understanding here:
When you do exec dbo.spTryTestA, you get
spTryTestA:1
Msg 50000, Level 16, State 1, Procedure spTryTestA, Line 5
spTryTestA:2
spTryTestA:3
This is because u raised high severity error and there is no error handling in proc. Now u execute this proc from Proc B,which has code like this:
create proc dbo.spTryTestB
as
--
begin try
raiserror('spTryTestB:1', 0, 1)
exec dbo.spTryTestA
raiserror('spTryTestB:2', 0, 1)
end try
begin catch
raiserror('spTryTestB:3', 0, 1)
end catch
Here first line of proc executes fine.In second line(exec Proc A) you receive error at raiserror('spTryTestA:2', 16, 1) from proc A. So only first line of proc A comes fine. Then you enter CATCH of proc B. And error is raised from CATCH. So it all is working as expected.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 9, 2010 at 5:34 am
this table shows whether errors are handled resumptively or not
remember that when calling execute on a connection (eg. outside of a procedure) the caller is "no try"
CURRENT SITUATION:
-----------------------------------------------------------------
caller called body catch
-----------------------------------------------------------------
no try no try resumptive -
no try try throw resumptive
try no try throw -
try try throw throw
-----------------------------------------------------------------
BETTER SITUATION:
-----------------------------------------------------------------
caller called body catch
-----------------------------------------------------------------
no try no try resumptive -
no try try throw throw
try no try resumptive -
try try throw throw
-----------------------------------------------------------------
"better" because:
- existing stored procedures do not break
- catch blocks work consistently
- overall less fragile
This is what I am learning before I start converting 100s of procedures to use (or work correctly with) TRY ... CATCH
[edit]
What it means is that I need to calculate the dependency tree for all modules that support TRY and alter the objects from the leaves to the root
as calling an old procedure from a new procedure might fail, but calling a new one from an old shouldn't - as long as I take into account the fact that catch blocks are resumptive unless there is a TRY in the callstack
July 9, 2010 at 9:00 am
To be fair, I do understand doobya's annoyance at having to change so many of his existing procedures if he's going to use TRY/CATCH. That would make me upset as well. But BOL says that's how it works, so that's how it works. A good rule of thumb seems to be: Don't count on code continuing to execute after an error, unless it is expressly created error-trapping code.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 9, 2010 at 3:34 pm
doobya (7/8/2010)
If you don't mind, I'll just go ahead and disagree with you on all those points. 😉 It all works exactly as I expect it to work according to BOL and I find no ambiguity in any of it.
dftt
Heh... brilliant. I work out your original example by hand based on what I found in BOL. I get exactly the same answer by hand as I eventually got by running your code which proves to me that BOL is spot on and you infer I'm a troll because [font="Arial Black"]you [/font]just don't get it? Good luck with your changes. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2010 at 3:52 pm
Double Forward Fourier Transforms ??
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 16 through 30 (of 81 total)
You must be logged in to reply to this topic. Login to reply