InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 00:37:53 GMT20http://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxHi, I have a question, can i execute a insert query inside the catch block?Regards,Arafath.Tue, 15 Jul 2008 06:03:05 GMTarafathhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxCan someone direct me to where I can find a list of all the errorcodes returned by 'ERROR_NUMBER()' inside a stored procedure?Thanks.AshleyWed, 16 Apr 2008 10:37:22 GMTAnthony Rogershttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxoops... 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 replyThnx.. <img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>Thu, 02 Feb 2006 21:46:00 GMTVasant Rajhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspx<P>Just curious as to the overhead to use Try/Catch within TSQL / stored procedure.</P><P>I'm assuming this is using the CLR and if you put this within a high volume, repeatedly used sproc, the performance hit involved?</P><P> </P>Thu, 02 Feb 2006 07:28:00 GMTTim OPryhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxThank 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? TIAHenrikThu, 02 Feb 2006 06:45:00 GMThenrik staun poulsenhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxXACT_STATE has 3 possible values.0 = there is no transaction1 = 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.Thu, 02 Feb 2006 03:27:00 GMTVasant Rajhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxGreat 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 TransactionBut I'm uncertain if I should add it to all my try-catch blocks. What do you think?Thu, 02 Feb 2006 01:27:00 GMThenrik staun poulsenhttp://www.sqlservercentral.com/Forums/Topic248571-227-1.aspxComments posted to this topic are about the content posted at <A HREF="http://www.sqlservercentral.com/columnists/rVasant/exceptionhandlinginsqlserver2005.asp">http://www.sqlservercentral.com/columnists/rVasant/exceptionhandlinginsqlserver2005.asp</A>Thu, 05 Jan 2006 11:33:00 GMTVasant Raj