Try Catch alters behaviour of existing procedures

Question

Post reply

Try Catch alters behaviour of existing procedures

Viewing 15 posts - 31 through 45 (of 81 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

The Dixie Flatline (7/9/2010)
Double Forward Fourier Transforms ??

😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 2

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

doobya, I would venture that most of the guru's here are saying this exact thing about you! :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DataDog Hall of Fame Points: 3305 More actions · Answer 3

His post did not add anything to the discussion - it was therefore light trolling

forum dwellers do tend to get upset when the status quo / comfort zone of "guru" helps "newbie" gets upset!

I don't mind being challenged - in fact I like being proved wrong as it means I have learnt something

Of course I can't be proved wrong in this case as I have only stated fact

What upsets the "gurus" is the implicit assumption in my post that "they didn't know"

But I bet there are lots of stored procedures being double checked as a result of my posting

I am therefore not a troll

TomThomson SSC Guru Points: 104773 More actions · Answer 4

doobya (7/12/2010)
His post did not add anything to the discussion - it was therefore light trolling
forum dwellers do tend to get upset when the status quo / comfort zone of "guru" helps "newbie" gets upset!
I don't mind being challenged - in fact I like being proved wrong as it means I have learnt something
Of course I can't be proved wrong in this case as I have only stated fact
What upsets the "gurus" is the implicit assumption in my post that "they didn't know"
But I bet there are lots of stored procedures being double checked as a result of my posting
I am therefore not a troll

I would be absolutely horrified if I thought that a large number of people were both so thoroughly unaware of the fundamental properties of try-catch error handling that they needed your posting to make them realise that in-line fail-and-continue error handling would be overridden and sufficiently arrogant that they started using this type of error handlingwithout bothering to learn its fundamental proerties, but I'm not horrified because I doubt very much that lots of stored prodcedures are being checked as a result of your posting (of course it's possible that by "lots" you mean the number you are now checking yourself, but surely you would have done that even if you hadn't posted?).

Tom

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I don't think anybody misunderstands anything after all this time. Yes, the flow of the procedure is interrupted when evoked from within a try/catch. So doobya is correct in saying its behavior is different. However, BOL clearly documents that TRY/CATCH will do that, and it seems that everyone else who has been involved in this thread isn't concerned.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

doobya (7/12/2010)
His post did not add anything to the discussion - it was therefore light trolling
forum dwellers do tend to get upset when the status quo / comfort zone of "guru" helps "newbie" gets upset!
I don't mind being challenged - in fact I like being proved wrong as it means I have learnt something
Of course I can't be proved wrong in this case as I have only stated fact
What upsets the "gurus" is the implicit assumption in my post that "they didn't know"
But I bet there are lots of stored procedures being double checked as a result of my posting
I am therefore not a troll

Know what? I've always known how Try/Catch works because it works just like BOL says. It would appear that you're the only one that "didn't know" and that you're the only one that may be upset. 😉

I can't prove a difference of opinion as to what an authority like BOL actually says. I can say that I believe that you are dead wrong and why I say such a thing to protect people that may read your conclusions from taking them seriously without running some tests of their own. While I agree that you're not a troll, I doubt that anyone who has spent more than about 10 or 20 minutes on the subject of Try/Catch is spending any time at all on changing their stored procedures based on what I believe to be a self documented misunderstanding on your part. Of course, neither of us can prove that here, either.

So, let's agree to disagree and you stop calling me bloody names.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DataDog Hall of Fame Points: 3305 More actions · Answer 7

I am bored of this topic now

But 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 ...

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

The Dixie Flatline (7/7/2010)
...
From Books Online (the section on RAISERROR):
When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:
Outside the scope of any TRY block.
With a severity of 10 or lower in a TRY block.
With a severity of 20 or higher that terminates the database connection.
...

Anyone other than doobya see anything ambiguous about that?

Anyone else other than doobya writing code that required rewriting because of Try/Catch?

It's rather sad to try to save face by saying "I'm bored." It reminds me of the patzers I used to play online chess against... who would just disconnect rather than resign.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

DataDog Hall of Fame Points: 3305 More actions · Answer 9

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block

ambiguity = "in a TRY block"

what does "in" mean?

what they could have said was "with a TRY block in the current call stack"

... as a statement in an old-style SP is not "in a TRY block"

ambiguity = are they talking about at creation-time, compile-time, run-time ???

Outside the scope of any TRY block

ambiguity = is the CATCH block considered part of the TRY block? it is in CSharp / Java ...

nowhere does it state "resumptive error handling is in effect in a catch block unless another try block is in the call stack"

the use of "any" is at least hinting at the presence of a TRY block in the call stack

but a hint is *ambiguous*

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

doobya (7/14/2010)
When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block
ambiguity = "in a TRY block"
what does "in" mean?
what they could have said was "with a TRY block in the current call stack"
... as a statement in an old-style SP is not "in a TRY block"
ambiguity = are they talking about at creation-time, compile-time, run-time ???

Hmm. In a TRY block, seems to me that if a stored procedure with no TRY/CATCH is executed within a TRY block, the entire stored procedure (and by definition the code contained within it) is in a TRY block, and that if an error with a severity of 11 or higher is raised, control is then transferred to the CATCH block assoctiated with the TRY in the calling procedure.

Sorry, doesn't look ambiguous to me.

Now, if the stored procedure ALSO has a TRY/CATCH block in which the statement raising the error with a severity of 11 or greater is inside a TRY block, control will be transferred to the CATCH block inside the stored procedure that is associated with the TRY block.

Again, not ambiguous.

DataDog Hall of Fame Points: 3305 More actions · Answer 11

in the following code which statements are in a TRY block?

create dbo.spBlah

as

raiserror('is this?', 16, 1)

begin try

raiserror('we all agree this one is', 16, 1)

end try

begin catch

raiserror('what about this?', 16, 1)

end catch

you see how the definition of "in a try block" could mean "at compile time, in syntax scope"

rather than "at run time, in call stack scope"

which means it is AMBIGUOUS

even your post said "seems" do you know what ambiguous means?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

doobya (7/14/2010)
in the following code which statements are in a TRY block?
create dbo.spBlah
as
raiserror('is this?', 16, 1)
begin try
raiserror('we all agree this one is', 16, 1)
end try
begin catch
raiserror('what about this?', 16, 1)
end catch
you see how the definition of "in a try block" could mean "at compile time, in syntax scope"
rather than "at run time, in call stack scope"
which means it is AMBIGUOUS
even your post said "seems" do you know what ambiguous means?

First of all, the above code won't pass a syntax check.

Second:

create dbo.spBlah

as

raiserror('is this?', 16, 1) -- NOT in a try block

begin try

raiserror('we all agree this one is', 16, 1) -- IN a try block

end try

begin catch

raiserror('what about this?', 16, 1) -- NOT in a try block

end catch

At least, based on what you posted.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

doobya (7/14/2010)
even your post said "seems" do you know what ambiguous means?

And on that note, I'm calling troll and dropping this thread. It's been enlightening. Ya'll have fun.

"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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Grant Fritchey (7/14/2010)
doobya (7/14/2010)
even your post said "seems" do you know what ambiguous means?
And on that note, I'm calling troll and dropping this thread. It's been enlightening. Ya'll have fun.

Missed that little bit.

Yes, I do know what ambiguous means. Sorry if I didn't use explicit language like you seem to like.

I have to agree with Grant, dftt.

Bye.

TomThomson SSC Guru Points: 104773 More actions · Answer 15

doobya (7/14/2010)
When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block
ambiguity = "in a TRY block"
what does "in" mean?
what they could have said was "with a TRY block in the current call stack"
... as a statement in an old-style SP is not "in a TRY block"
ambiguity = are they talking about at creation-time, compile-time, run-time ???

No ambiguity: the text you quote says "When RAISERROR is run ..." so it is talking about running in a try block: not about static compile time context but about dynamic execution context.

Tom