Exception Handling in SQL Server 2005

  • Vasant Raj

    SSCommitted

    Points: 1835

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

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6349

    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

    SSCommitted

    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

    SSCertifiable

    Points: 6349

    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

  • Tim OPry

    SSCrazy

    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

    SSCommitted

    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

    Thnx..

  • Anthony Rogers

    Grasshopper

    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?

    Thanks.

    Ashley

  • arafath

    SSC Enthusiast

    Points: 100

    Hi,

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

    Regards,

    Arafath.

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

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