@@TRANCOUNT (Rollback) vs. XACT_STATE

  • What is the difference between these two approaches? The use of @@TRANCOUNT seems legitmate to me but looking at the approach used by in the URL makes me wonder if I have missed something. (The URL approach certainly looks more thorough but I don't know if it is "btter" than the @@TRANCOUNT approach).

    TIA,

    Barkingdog

    1.

    BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK

    ...

    ...

    2. From http://msdn.microsoft.com/en-us/library/ms179296.aspx

    BEGIN CATCH

    SELECT

    ERROR_NUMBER() as ErrorNumber,

    ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.

    -- XACT_STATE = 0 means there is no transaction and

    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.

    IF (XACT_STATE()) = -1

    BEGIN

    PRINT

    N'The transaction is in an uncommittable state. ' +

    'Rolling back transaction.'

    ROLLBACK TRANSACTION;

    END;

    -- Test whether the transaction is active and valid.

    IF (XACT_STATE()) = 1

    BEGIN

    PRINT

    N'The transaction is committable. ' +

    'Committing transaction.'

    COMMIT TRANSACTION;

    END;

    END CATCH;

  • The use of the two is slightly different. If you look at the XACT_STATE code, you'll notice that it has a commit statement if XACT_STATE = 1.

    If you want to roll back, the @@Trancount code will do it for you. If you want to commit under certain circumstances and rollback in other circumstances, you can use the other version.

    Personally, if I need to commit under certain circumstances and rollback under other, I controll that in the main body of the proc, and just roll back any/all uncommitted transactions in any Catch block. But I can see where it might, in theory, be useful to use the other way. Just can't think of a practical use for it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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