Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Try Catch alters behaviour of existing procedures Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
this code shows that calling an existing stored procedure inside a try block alters its behaviour
that seems like a bug / mistake to me

go
create proc dbo.spTryTestA
as
--
raiserror('spTryTestA:1', 0, 1)
raiserror('spTryTestA:2', 16, 1)
raiserror('spTryTestA:3', 0, 1)
--(end)
go
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
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

this means that all SPs need to be redesigned to detect whether they are called from inside try block or not!

in my databases - in most cases it is far easier and cleaner to avoid try catch than to use it

for example:

old style:
if @@error <> 0 or @@rowcount < 1 goto label_rollback

new style:
if @@rowcount < 1 raiserror('@@rowcount < 1', 16, 1)

overall the try catch actually increases the amount of code you need to write
and can decrease the readability and reliability

as I have 100s of SPs from before TRY CATCH I say it is too risky to use try catch and there is nothing to be gained anyway
it's another T-SQL fail - keeps us in work though *-)
Post #948167
Posted Tuesday, July 6, 2010 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
Perhaps I am being dense, but the entire purpose of TRY...CATCH is to change behavior. Now, I could understand if you don't like HOW it changes behavior, but I am having a hard time understanding what you'd expect it to do if it doesn't change behavior.

I do find it has some flaws, but still - it seems to be to be a huge step forward from continuously having to check @@ERROR after each operation lest you miss a single-non-breaking exception.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #948215
Posted Tuesday, July 6, 2010 4:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
the problem is that it changes the behaviour of OTHER pre-existing stored procedures
that already have a defined and tested behaviour

it breaks them

a simple example:

if @@error <> 0 goto label_do_something_else

won't work if the sp is called from a TRY block
but will work if called normally

so a pre-defined behaviour has changed in a subtle way

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!
Post #948231
Posted Tuesday, July 6, 2010 4:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
Matt Miller (#4) (7/6/2010)I do find it has some flaws, but still - it seems to be to be a huge step forward from continuously having to check @@ERROR after each operation lest you miss a single-non-breaking exception.


but you still have to check

if @@rowcount < 1

after updates and deletes

and there isn't much difference between

if @@error <> 0 or @@rowcount < 1 goto label_rollback
and
if @@rowcount < 1 raiserror('oh no', 16, 1)

plus you still need to check XACT_STATE
and even need to check for @@trancount > 1

and I find that once a big SP has been converted to use TRY CATCH
it is usually more verbose and less readable

and why no THROW?

instead I use this:

create proc dbo.spThrowError
as
--
declare @errmsg nvarchar(2048)
declare @errno int
declare @errseverity int
declare @errstate int
declare @errline int
declare @errproc nvarchar(126)
--
select @errmsg = error_message()
, @errno = error_number()
, @errseverity = error_severity()
, @errstate = error_state()
, @errline = error_line()
, @errproc = coalesce(error_procedure(), '-')
--
raiserror (

@errmsg -- msg
, @errseverity -- severity
, 1 -- state
, @errno -- argument
, @errseverity -- argument
, @errstate -- argument
, @errproc -- argument
, @errline -- argument
)
--(end)

is there anyway to fix the tabs on this website - the combination of proportional font in the editor
and 8 space tabs in the preview is a nightmare - the default for TSQL has been 4 spaces for years
Post #948234
Posted Wednesday, July 7, 2010 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #948488
Posted Wednesday, July 7, 2010 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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
Post #948505
Posted Wednesday, July 7, 2010 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #948547
Posted Wednesday, July 7, 2010 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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
Post #948613
Posted Wednesday, July 7, 2010 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #948619
Posted Wednesday, July 7, 2010 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
I'm sorry to have to say it - but you still do not understand
OO
Post #948795
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse