Post reply

Exception Handling in SQL Server 2005

  • January 5, 2006 at 11:33 am

    #166671

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/exceptionhandlinginsqlserver2005.asp

  • February 2, 2006 at 1:27 am

    #618296

    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?

  • February 2, 2006 at 3:27 am

    #618322

    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.

  • February 2, 2006 at 6:45 am

    #618368

    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

  • February 2, 2006 at 7:28 am

    #618388

    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?

     

  • February 2, 2006 at 9:46 pm

    #618572

    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..

  • April 16, 2008 at 10:37 am

    #803209

    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

  • July 15, 2008 at 6:03 am

    #842991

    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