sql server - sql try/catch rollback/commit - preventing erroneous commit after rollback

Question

I am trying to write an MS sql script that has a transaction and a try/catch block. If it catches an exception, the transaction is rolled back. If not, the transaction is committed. I have seen a few different websites saying to do it like this:

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
end catch

commit transaction

But won't we still hit the "commit transaction" line even in the case of catching an exception? Won't this lead to a SQL error because the transaction has already been rolled back? I think it should be done like this:

declare @success bit = 1

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
    set @success = 0
end catch

if(@success = 1)
begin
    commit transaction
end

Howcome the commonly-posted solution does not include the @success variable? Is there no sql error that happens as a result of committing a transaction that has already been rolled back? Am I incorrect in saying that the "commit transaction" line of the first code example will still be hit in the case of catching an exception?

Answer 1

I always thought this was one of the better articles on the subject. It includes the following example that I think makes it clear and includes the frequently overlooked @@trancount which is needed for reliable nested transactions

PRINT 'BEFORE TRY'
BEGIN TRY
    BEGIN TRAN
     PRINT 'First Statement in the TRY block'
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
     UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
     PRINT 'Last Statement in the TRY block'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN;

    THROW; -- raise error to the client
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO