Exception Handling in SQL Server 2005
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/exceptionhandlinginsqlserver2005.asp
-
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?
-
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.
-
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
-
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?
-
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..
-
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
-
Hi,
I have a question, can i execute a insert query inside the catch block?
Regards,
Arafath.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply