July 14, 2010 at 2:08 pm
doobya (7/14/2010)
I am bored of this topic nowBut still expect anybody saying that BOL is *not* ambiguous to provide a quote to prove it
(I cannot provide a quote to prove that it *is* ambiguous without posting the whole thing)
But you *should* be able to ...
Heh... you've already provided the necessary quotes but, unlike all the others, insist that the wording is ambiguous.
Since I'm actually going to have the extreme pleasure of doing some 12 oz. curls with Grant in a couple of hours, I bid you ado and wish you well with your rewrites. Seriously... not making fun of you. Good luck.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2010 at 2:36 pm
doobya (7/6/2010)
this code shows that calling an existing stored procedure inside a try block alters its behaviourthat seems like a bug / mistake to me
gocreate procdbo.spTryTestA
as
--
raiserror('spTryTestA:1', 0, 1)
raiserror('spTryTestA:2', 16, 1)
raiserror('spTryTestA:3', 0, 1)
--(end)
go
create procdbo.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
go
exec dbo.spTryTestA
exec dbo.spTryTestB
go
spTryTestA:1
Msg 50000, Level 16, State 1, Procedure spTryTestA, Line 6
spTryTestA:2
spTryTestA:3 << codepath reaches here
spTryTestB:1
spTryTestA:1 << oh, codepath exits
spTryTestB:3
I have to agree with the "others". First of all, the code in your second example does not exit on "oh, codepath exists". It successfully executes the RAISERROR("..",0,1) in procA, and then "exits" on the next statement (the RAISERROR("...", 16,1).
July 14, 2010 at 2:54 pm
This is probably the most amazing thread that I've ever seen on any forum. I have to give the OP a lot of credit. Despite all 11 respondents (who I would guess average 8+ years professional db programming experience) disagreeing in unconditional terms, he unabashedly sticks to his claim. Everyone else is wrong and only he gets it. We are all too dumb to see the hidden ambiguity in the obvious. And I'm not even sure what that means!
July 14, 2010 at 4:28 pm
that is the output from query analyzer
spTryTestA:1
Msg 50000, Level 16, State 1, Procedure spTryTestA, Line 6
spTryTestA:2
spTryTestA:3 << codepath reaches here
spTryTestB:1
spTryTestA:1 << oh, codepath exits
spTryTestB:3
It is not incorrect - I am pointing out the difference in the Query Analyzer OUTPUT due to the TRY block
As you well know 😉 raiserror does not output messages to the connection WHEN THEY ARE CAUGHT
BOL is unambiguous on this point LOL
the message from a raiserror(16) will not be output to the connection when "in a try block"
if it is clearer:
spTryTestB:1
spTryTestA:1
raiserror(16) occurs (there is no output) and oh, codepath exits BEFORE spTryTestA:2
spTryTestB:3
See?
The only point in question is whether I should have posted something so "obvious" not whether my post is correct
July 15, 2010 at 3:40 am
doobya (7/14/2010)
spTryTestB:1spTryTestA:1
raiserror(16) occurs (there is no output) and oh, codepath exits BEFORE spTryTestA:2
spTryTestB:3
See?
It doesn't exit before :2, it exits (because of the exception thrown) on :2
July 15, 2010 at 3:46 am
And another thing; The difference between "old style" execution and execution in a TRY CATCH block is completely obvious and logical. I don't see why you think that is a problem. You cannot put your code inside TRY CATCH blocks and expect it to behave the same as it did before; the same way as you cannot expect old style VB6 code with a "on error resume next" to behave the same if you wrap it inside the new try... catch in VB.NET.
July 15, 2010 at 4:19 am
VB6 is a good example, because you can use
On Error Resume Next
or
On Error Goto 0
at the top of any module and it is will be PREDICTABLE and CONSISTENT regardless of how or who calls
the only way to achieve this predictability with T-SQL is to write any code declared outside of a local TRY block
so it works BOTH in a resumptive context AND in a non-resumptive context - which isn't always easy
that is why it is bad design
that is why it is non-obvious
the only robust solutions I can think of to this problem (which applies mainly to multi-line catch blocks) are:
wrap statements in a CATCH block in *another* TRY CATCH block
--or--
wrap every TRY CATCH block in another TRY CATCH block
--or--
wrap every statement in a CATCH block in a TRY CATCH block
--or--
carefully consider the MULTIPLE codepaths that may be taken
none of which is very sensible - if they had used my proposed technique all of these problems would disappear
and TSQL would become predictable and consistent
and no I don't believe everybody in this post fully appreciates the problem or its implications
if they did they wouldn't be arguing with me
(and your previous point is moot - I am simply pointing out where the SQL output changes - not saying that raiserror(16) isn't executed)
July 15, 2010 at 5:40 am
doobya (7/15/2010)
VB6 is a good example, because you can use.....
You seem to have a singular point of view in that no one agrees with you.
On Error Resume Next
I would like to see more detailed explanation why you think this is PREDICTABLE and CONSISTENT.
that is why it is non-obvious
It works how it is supposed to, execute code in the TRY block until an error then procede to the CATCH block.
Note that your statement about changing 'behaviour' of other pre-existing stored procedures is correct, any code change at any level of nested code in any coding language can change 'behaviour'.
This is also true in VB6 with 'On Error Resume Next'.
Quote from BOL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.
What is not obvious about that?
Far away is close at hand in the images of elsewhere.
Anon.
July 15, 2010 at 6:05 am
these are dreary non-arguments
in VB6 you can control whether a given block of code is resumptive or not
in T-SQL only code that is EXPLICITLY DECLARED INSIDE A LOCAL TRY BLOCK is *guaranteed* to be non-resumptive
any other code MAY or MAY NOT be resumptive depending on runtime circumstances
this has all been explained
the quote regarding C# doesn't support your argument because in CSharp
there is no resumptive exception handling - because there is an implicit top level TRY block created by the runtime
therefore the codepath taken by a method would be the same regardless of whether there are additional TRY blocks in the call stack
(C++ I have no clue about)
I am sorry but your post is a total fail 🙁
July 15, 2010 at 6:22 am
I'm beginning to wonder what this thread is about. Everyone here agrees that code executed in a try block (ie invoked from a try block) will behave differently under some error conditions from the same code executed when not invoked from a try block. There would be absolutely no point in having try blocks if it didn't, this change of behaviour is the whole raison d'être of try blocks. The OP apparently finds this surprising. He claimed that BoL doesn't state it clearly (although he has quoted sections of BoL that do state it clearly). In his last post we are presented with a different claim, that the fact that the behaviour under error conditions is altered by having a try block is bad design and it would be better if the code did the same when invoked from a try block as it does wwhen invoked without any try block.
I suspect that his concern is that error information for the error that caused the catch block to be entered is absorbed by the catch block and not passed on to the ouside world. If so, it's a matter of not writing the catch block correctly: if it is required to pass the error information up to a higher level, the cach block can do that: it can get the information from the ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() functions, do whatever else it needs to do, and pass the error information on using either a result set or a call of raiserror (with a severity lower than 11 if it wants to allow execution to continue after the catch block or greater than 10 if it wants to allow an outer try-catch construct instead). This is all documented (with examples) in BoL.
Tom
July 15, 2010 at 6:43 am
doobya (7/15/2010)
in VB6 you can control whether a given block of code is resumptive or not
Not entirely true, it 'depends' on the definition of 'a block of code' and where 'On Error' is used.
Far away is close at hand in the images of elsewhere.
Anon.
July 15, 2010 at 6:45 am
doobya (7/15/2010)
the quote regarding C# doesn't support your argument because in CSharpthere is no resumptive exception handling - because there is an implicit top level TRY block created by the runtime
therefore the codepath taken by a method would be the same regardless of whether there are additional TRY blocks in the call stack
If I have some code like
try {x = y/z}
catch (e) {
logerror(e);
x=MAXINT;
if (y<0) x=-x} else {if (y=0)x=0}
}
the effect of calling it wwhen z is 0 is NOT to terminate execution of the program reporting a zero-divide error: the try-catch construct has changed the behaviour of the code "x=y/z". If I do my divide in a separate proc and call that proc from the try, the try-catch construct has modified the behaviour of that proc. So clearly the situation is just as it is in T-SQL - a module will behave differently in error conditions when invoked from a try block. This applies in C#, C++, VB, Java, Javascript, JScript and every other language which uses the try-catch construct.
(C++ I have no clue about)
Nor about C#, as you demonstrate above.
I am sorry but your post is a total fail 🙁
Only if you had changed "your" to "my" would that sentence have been correct! The post from David Burrows was far from the total fail you claim.
Tom
July 15, 2010 at 6:51 am
Tom.Thomson (7/15/2010)
The post from David Burrows was far from the total fail you claim.
Thanks Tom 🙂
I think this thread is on the slippery slope to oblivion 😉
Far away is close at hand in the images of elsewhere.
Anon.
July 15, 2010 at 6:53 am
doobya (7/15/2010)
these are dreary non-argumentsin VB6 you can control whether a given block of code is resumptive or not
in T-SQL only code that is EXPLICITLY DECLARED INSIDE A LOCAL TRY BLOCK is *guaranteed* to be non-resumptive
any other code MAY or MAY NOT be resumptive depending on runtime circumstances
this has all been explained
the quote regarding C# doesn't support your argument because in CSharp
there is no resumptive exception handling - because there is an implicit top level TRY block created by the runtime
therefore the codepath taken by a method would be the same regardless of whether there are additional TRY blocks in the call stack
(C++ I have no clue about)
I am sorry but your post is a total fail 🙁
I'm sure that I will regret this, but I have to clear up two statements here.
First, there is NO "implicit Try..Catch" in C#. If you do NOT put a Try..Catch in the code, it will bubble the exception all the way back to the Framework, and fail the app with an un-handled exception. Period.
Second, you are using VB6 as an example. Well, to compare apples to apples, T-SQL 2000 and T-SQL 2005 is the same path as VB6 to VB .Net. VB .Net NO LONGER HAS On Error Resume Next. You MUST re-design your applications to manually implement resumptive error processing (otherwise known as "Ignore the Error"). And that, IMO, is what you are really arguing for here, that when you use Try..Catch, you can no longer assume that the error will be reported and then ignored. It means that you can not rely on Raiserror to do simple messaging, you must redesign so that your severity levels are appropriate to what you are accomplishing. If you simply want to message, change the severity level to 10 (Informational) and the behavior will be EXACTLY what is was without the Try..Catch. But if the level is 11 or higher (defined as actual ERRORS), then the code execution will be halted prior to any data damage occuring due to UNFORSEEN circumstances.
After all, coding best practices are to NOT RELY on Try..Catch to handle common situations, those should be specifically coded for, as Try..Catch does introduce execution overhead. Try..Catch is for handling unpredictable circumstances when they occur.
~End of rant.
July 15, 2010 at 11:57 am
First, there is NO "implicit Try..Catch" in C#. If you do NOT put a Try..Catch in the code, it will bubble the exception all the way back to the Framework, and fail the app with an un-handled exception
so you are saying the dot net exception isn't caught by the runtime
but at the same time IS caught by runtime
almost as if ...
try { << framework code
Program.Main(); << calling user entry point
} catch (Exception exc) { << hmmm caught by invisible aka implicit catch
// dot net runtime closes down appdomain and safely shuts down win32 process it is running inside ...
}//try
you guys are just throwing yourselves on spikes
the fact is simple - the error handling in T-SQL is unique
AFAIK there is no other language with an identical system
while it is true that VB6 does share a similar problem in that a function or subroutine
that doesn't explicitly specify an On Error Goto or Resume can catch out the unwary programmer
this point is NOT in T-SQLs favour - but against it
furthermore VB6 doesn't have the problem of CHANGING the error handling mode when entering a CATCH block
as this code demonstrates:
Sub TestErrors()
On Error GoTo LabelError
Err.Raise 1100
Debug.Print "does not reach here"
LabelError:
Debug.Print "reaches here"
Err.Raise 1100
Debug.Print "does not get here"
End Sub
the debug output from this will be "reaches here"
in the T-SQL equivalent:
create proc dbo.spMuppets
as
begin try
raiserror('muppets', 16, 1)
end try
begin catch
raiserror('muppets', 16, 1)
raiserror('can reach this point depending ...', 0, 1)
end catch
the error handling switches from non-resumptive to resumptive
UNLESS the call stack includes a TRY block
in which case it stays non-resumptive
one solution to this problem is quite simple, but a bit messy:
create proc dbo.spMuppets
as
begin try
raiserror('muppets', 16, 1)
end try
begin catch
begin try
raiserror('muppets', 16, 1)
raiserror('now this is always unreachable ...', 0, 1)
end try
begin catch
exec dbo.spThrowError
end catch
end catch
which also means there is no way to guarantee resumptive error handling without this (same as csharp)
create proc dbo.spMuppets
as
begin try
raiserror('muppets', 16, 1)
end try
begin catch
begin try
raiserror('muppets', 16, 1)
end try
begin catch
end catch
begin try
raiserror('now this is reachable ...', 0, 1)
end try
begin catch
end catch
end catch
it is impossible to state that I am wrong
and impossible to say that this is good design
and, due to its uniqueness, impossible to say that it is "obvious"
also anybody harping on about severity levels does not understand what I am saying
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply