TRY..CATCh & XACT_ABORT

  • I generally use the following template for error handling in SQL Server 2008

    CREATE PROCEDURE Sample

    As

    BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

    BEGIN TRAN

    -- Multiple DML

    INSERT INTO TabA ....

    INSERT INTO TabB

    COMMIT TRAN

    RETURN(50)

    END TRY

    BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRAN

    RAISEERROR(......)

    RETURN(10)

    END CATCH

    END

    I also found in MSDN it is suggested to use SET XACT_ABORT ON . I can understand its purpose with reference to SQL Server 2000. I also know we can COMMIT a transaction even in CATCH block based on XACT_STATE().

    If as per business and Design we are not willing to COMMIT any Transaction in CATCH block Then

    -- TRAY .. CATCH with @@TRANCOUNT check in Catch block is sufficient ?

    -- Is it beneficial to use XACT_ABORT and XACT_STATE in comparison to template I provided ?

    Thanks in Advance.

    Regards

    BB

  • (In case someone else stumbles across this)

    I believe one fundamental difference (if I'm reading BOL right - if not, someone please correct this) is that XACT_STATE can detect uncommittable transactions (XACT_STATE = -1) whereas @@TRANCOUNT detects if there are active transactions, but not whether they are committable.

    So from my limited understanding, you'd probably use @@TRANCOUNT to rollback active transactions in a CATCH block no matter what, and XACT_STATE to commit if possible despite the error.

    Update BOL also states XACT_STATE does not detect nested transactions while @@TRANCOUNT does.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

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

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