Try Catch alters behaviour of existing procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Try Catch alters behaviour of existing...

82 posts, Page 2 of 9

««1 2 3 4 5 »»»

Try Catch alters behaviour of existing procedures

Rate Topic

Display Mode

Topic Options

Author

Message

The Dixie Flatline

Posted Wednesday, July 07, 2010 2:37 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

I understand. But you have control of the behavior based on the error severity level.

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.

In your example, change the error severity to 9 instead of 16 and you get the consistent behavior you are after. The various behaviors based on error severity are by design. In some cases a procedure cannot and should not proceed after a severe error.

__________________________________________________

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

Post #948841

DataDog

Posted Wednesday, July 07, 2010 3:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146, Visits: 421

The Dixie Flatline (7/7/2010)In your example, change the error severity to 9 instead of 16 and you get the consistent behavior you are after. The various behaviors based on error severity are by design. In some cases a procedure cannot and should not proceed after a severe error.

I cannot control the severity level of errors

that example is contrived to prove that the codepath is altered

in real life it will be a DML statement that is raising the error

update dbo.Table
set [Field] = 'value'
where [OtherField] = 'otherValue'
if @@error <> 0 or @@rowcount <> 1 goto label_do_something --<< when this SP is called from another SP within a TRY block it may not reach this line

Post #948883

bteraberry

Posted Wednesday, July 07, 2010 4:50 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103, Visits: 1,170

doobya,

Let me give you a few suggestions:

#1. Consider that maybe it's you who isn't understanding what's being said. You're arguing with someone who writes top notch books on SQL Server. I'm personally not inclined to think that he's the one that doesn't get it.

#2. Take a deep breath and understand how TRY...CATCH blocks are designed to work, not how you think they should work had you designed SQL Server yourself. The whole point of the block is to exit to the CATCH when the error is encountered rather than continuing to process. What you're doing is like complaining about how your TV no longer works after you blow the circuit by purposely overloading it. Of course it doesn't work, it's designed specifically not to work.

If you want 'Y' to happen regardless of what happens with 'X' then don't put it in the same TRY.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help

Post #948904

DataDog

Posted Wednesday, July 07, 2010 5:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146, Visits: 421

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

my very first post explains this with working code

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

but the purpose of my post is to warn people - beware - this behaviour is not what the majority of developers would expect
and WILL CATCH YOU OUT (no pun intended) especially if you have an existing library of stored procedures that WILL BREAK if they are called with a call stack that includes a TRY block at any level

you can not simply convert a SQL 2000 database to SQL 2005 and expect to get away with it

the only safe approach is to rewrite ALL existing stored procedures to work whether called within a TRY block or not ...

Is that common knowledge or not?

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

Post #948912

bteraberry

Posted Wednesday, July 07, 2010 5:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103, Visits: 1,170

Also, you can take a look at what happens in your example when you change the proc dbo.spTryTestA:

create proc dbo.spTryTestA
as
--
begin try
	raiserror('spTryTestA:1', 0, 1)
end try
begin catch
	raiserror('spTryTestA:1 failed', 0, 1)
end catch
begin try
	raiserror('spTryTestA:2', 16, 1)
end try
begin catch
	raiserror('spTryTestA:2 failed', 0, 1)
end catch
begin try
	raiserror('spTryTestA:3', 0, 1)
end try
begin catch
	raiserror('spTryTestA:3 failed', 0, 1)
end catch

--(end)
go

This returns the same results either way. The issue in your example is that you've got a call to a ghetto proc within a TRY block. Now, there is nothing to be ashamed of ... we all have old code around that doesn't truly have error handling (checking @@ERROR is not proper error handling), but you can't make calls from that code from within a TRY block if you don't want the errors in the code to bubble up to the TRY. If you want to do execute legacy code, it's a pretty simple matter to move the call outside the TRY block.

Now, if your original stated opinion was that TRY...CATCH doesn't work well to make calls to archaic procs then I would wholly agree.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help

Post #948915

bteraberry

Posted Wednesday, July 07, 2010 5:38 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103, Visits: 1,170

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help

Post #948918

Jeff Moden

Posted Wednesday, July 07, 2010 5:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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 DOES 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. Wink

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #948919

DataDog

Posted Wednesday, July 07, 2010 5:56 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146, Visits: 421

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

Post #948920

bteraberry

Posted Wednesday, July 07, 2010 6:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103, Visits: 1,170

doobya (7/7/2010)

[quote]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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help

Post #948923

L' Eomot Inversé

Posted Wednesday, July 07, 2010 6:45 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 7,078, Visits: 7,119

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #948930

« Prev Topic | Next Topic »

82 posts, Page 2 of 9

««1 2 3 4 5 »»»

Permissions