Exception Handling in SQL Server 2005

  • Vasant Raj


    Points: 1835

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

  • Henrik Staun Poulsen


    Points: 6376

    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?

  • Vasant Raj


    Points: 1835

    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.

  • Henrik Staun Poulsen


    Points: 6376

    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?



  • Tim OPry


    Points: 2988

    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?


  • Vasant Raj


    Points: 1835

    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


  • Anthony Rogers


    Points: 17

    Can someone direct me to where I can find a list of all the error

    codes returned by 'ERROR_NUMBER()' inside a stored procedure?



  • arafath

    SSC Enthusiast

    Points: 100


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



Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply