@@TRANCOUNT (T-SQL)
Many good resources for @@TRANCOUNT:
https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver15
https://sqlserverrider.wordpress.com/2013/02/04/trancount-sql-server/
https://stackoverflow.com/questions/13983467/when-its-necessary-to-check-trancount-0-in-try-catch-block
https://dba.stackexchange.com/questions/199361/what-does-if-trancount-0-commit-tran-mean
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
In explicit transaction mode with nested transactions, each BEGIN TRANSACTION must correspond to a COMMIT TRANSACTION. As each new transaction starts with BEGIN TRANSACTION, the @@TRANCOUNT variable increments by 1 and each COMMIT TRANSACTION decrements it by 1. The complete transaction does not get written to disk and committed completely until @@TRANCOUNT is 0.
Normally, a ROLLBACK TRANSACTION resets the value of @@TRANCOUNT to 0. However, when a transaction rolls back to a savepoint, @@TRANCOUNT is not reset. The SAVE TRANSACTION statement also has no effect on @@TRANCOUNT. In other words, Savepoints allow you to partially rollback a transaction to a named location. Neitherthe SAVE TRANSACTION nor the ROLLBACK TRANSACTION statements havean effect on the @@TRANCOUNT variable (as long as the transaction rolls back toa specific savepoint rather than completely).
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the transaction count by 1.
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
-- The COMMIT statement will decrement the transaction count by 1.
COMMIT PRINT @@TRANCOUNT
COMMIT PRINT @@TRANCOUNT
--Results --0 --1 --2 --1 --0
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the transaction count by 1.
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
-- The ROLLBACK statement will clear the @@TRANCOUNT variable to 0 because all active transactions will be rolled back.
ROLLBACK PRINT @@TRANCOUNT
--Results --0 --1 --2 --0
You are developing a stored procedure with the following requirements: ✑ Accepts an integer as input and inserts the value into a table. ✑ Ensures new transactions are committed as part of the outer transactions. ✑ Preserves existing transactions if the transaction in the procedure fails. ✑ If the transaction in the procedure fails, rollback the transaction.
Box 1: @TranCount> 0 - Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error. Box 2: @TranCount = 0 - -- @TranCount = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.
At that point @@TRANCOUNT will be 1 or above, since we explicitly started a transaction if one wasn't already started. The point of the code where Box2 exists is checking "was there a transaction before I started? if not, we are safe to commit" to avoid committing transactions started outside the stored procedure. See: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15
Box 3: XACT_STATE() <> -1 - If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure. References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017
