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

Exception Handling in SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, January 5, 2006 11:33 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/exceptionhandlinginsqlserver2005.asp
Post #248571
Posted Thursday, February 2, 2006 1:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 1,430, Visits: 1,025
Great article, but I think you forget one thing:
What about xact_state() ?

I've seen somewhere (might have been BOL) that this code should work:
if (XAXT_State()) = -1 RollBack Transaction

But I'm uncertain if I should add it to all my try-catch blocks.
What do you think?



Post #255183
Posted Thursday, February 2, 2006 3:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
XACT_STATE has 3 possible values.
0 = there is no transaction
1 = Transaction can be committed
-1 = Transaction should be rolled back.

So, if your try-catch block needs to maintain TRANSACTIONs then your CATCH block should check for (XACT_STATE()) = -1 rolling back your transaction. You can start checking the condition (XACT_STATE()) <> 0 in your CATCH block.
Post #255211
Posted Thursday, February 2, 2006 6:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 1,430, Visits: 1,025
Thank you very much for replying, but according to my BOL it has -1, 0 and 1 as values.
Where do you get your 0,1,2 values from?
TIA
Henrik



Post #255269
Posted Thursday, February 2, 2006 7:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 31, 2014 5:16 AM
Points: 160, Visits: 247

Just curious as to the overhead to use Try/Catch within TSQL / stored procedure.

I'm assuming this is using the CLR and if you put this within a high volume, repeatedly used sproc, the performance hit involved?

 




Post #255291
Posted Thursday, February 2, 2006 9:46 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
oops... sorry... a mistake...
XACT_STATE states are 0,1 and -1 as you mentioned...
I used XACT_STATE in some examples and while checking for state 1, i had the condition
XACT_STATE >= 1 on my mind. I m updating my reply
Thnx..
Post #255513
Posted Wednesday, April 16, 2008 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 8:53 PM
Points: 1, Visits: 11
Can someone direct me to where I can find a list of all the error
codes returned by 'ERROR_NUMBER()' inside a stored procedure?

Thanks.
Ashley

Post #485818
Posted Tuesday, July 15, 2008 6:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 10:08 AM
Points: 62, Visits: 44
Hi,

I have a question, can i execute a insert query inside the catch block?

Regards,
Arafath.
Post #534263
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse