Search code examples
sql-serversql-server-2008t-sqltransactionstry-catch

When it's necessary to check @@trancount > 0 in try catch block?


Sometimes I saw the following code snippet. When is the if @@trancount > 0 necessary with begin try? Both of them? Or it's a safe way(best practice) to check it always in case it's rollback before the check?

begin tran
begin try
  ... just several lines of sql ...
  if @@trancount > 0 commit tran
end try
begin catch
  if @@trancount > 0 rollback tran
end catch

Solution

  • I can think of a few scenarios to consider when dealing with @@trancount:

    1. The current transaction was called from another stored procedure which had its own transaction
    2. The current transaction was called by some .NET code with its own transaction
    3. The current transaction is the only transaction

    I believe Remus Rusanu's Exception handling and nested transactions handles all these possibilities.