SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Try Catch alters behaviour of existing procedures


Try Catch alters behaviour of existing procedures

Author
Message
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 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 *-)
Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30981 Visits: 19011
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?
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 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!
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102315 Visits: 33014
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
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102315 Visits: 33014
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
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102315 Visits: 33014
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
DataDog
DataDog
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 426
I'm sorry to have to say it - but you still do not understand
OO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search