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?
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