Exception Handling in SQL Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Raj Vasant » Exception Handling in SQL Server 2005

Exception Handling in SQL Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Vasant Raj

Posted Thursday, January 05, 2006 11:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 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

henrik staun poulsen

Posted Thursday, February 02, 2006 1:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 1,131, Visits: 855

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

Vasant Raj

Posted Thursday, February 02, 2006 3:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 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

henrik staun poulsen

Posted Thursday, February 02, 2006 6:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 1,131, Visits: 855

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

Tim OPry

Posted Thursday, February 02, 2006 7:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 12, 2013 5:54 AM
Points: 159, Visits: 239

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

Vasant Raj

Posted Thursday, February 02, 2006 9:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 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

Anthony Rogers

Posted Wednesday, April 16, 2008 10:37 AM

Forum 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

arafath

Posted Tuesday, July 15, 2008 6:03 AM

Valued 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 »

Permissions