Conditional Rollback

I have inherited a lot of SQL2005 TSQL, that has the following generic catch structure:

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

From BOL, @.@.TRANCOUNT returns the number of active transactions for the current connection and XACT_STATE()) is a scalar function that reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

My question is: Should the tests be OR'd rather than AND'd?

The line in question would then read:

IF @.@.TRANCOUNT > 0 OR (XACT_STATE()) <> 0

BOL says,

Both the XACT_STATE and @.@.TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @.@.TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

ie,

Instead of using the @.@.Trancount you can always check the XACT_STATE. Bcs @.@.TRANCOUNT never tells you wheather you have to do Commit or rollback. It only gives the number of transaction begin.

But

XACT_STATE = 0 no transaction active

XACT_STATE = 1 active transaction available & can be writable & commitable/rollbackable -- Commit/rollback

XACT_STATE = -1 active transaction available & uncommitable -- Rollbacked

|||

Thankyou Manivannan,

The TSQL in question runs after an error within the try section has been caught. My question relates as whether the functions should be OR'd instead of AND'd. Possibly only one of them should be used.

|||

Ok now, My direct answer is XACT_STATE is more enough to use....(trustable). Need not have or/and only XACT_STATE is enough to validate the condition.

|||

If I may summerise

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH
should become

BEGIN CATCH
IF (XACT_STATE()) <> 0 -- < Suggested simpler construction.
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

Is this your recommendation?

|||Yes...Exactly..