SQL Server - correct syntax for transaction

Question

I'm trying to create a transaction for multiple inserts, if one fails I want all to be rolled back. Based on examples i've found i've got this

BEGIN TRY
BEGIN TRAN
INSERT1
INSERT2
INSERT3
INSERT4
COMMIT TRAN
END TRY
BEGIN CATCH
 IF (@@TRANCOUNT > 0)  
BEGIN  
    ROLLBACK TRANSACTION;  
END; 
INSERT INTO Errors (errornumber, errorseverity, errorstate, errorprocedure, errorline, errormessage)
    VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE())
END CATCH

In my example INSERT4 fails. The other inserts seem to rollback correctly but I get this error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Is the syntax correct? I've seen other examples which test IF (XACT_STATE()) = -1 instead of @@TRANCOUNT > 0

Answer 1

This here will rollback all your insert statements even if one only fails.

As you can see im truncating my tables first and then trying to insert string values into a int column. This ofcause fails, and then the statement rollsback. As you can see i have still results in my tran1 table and tran2 table even though i truncated them first.

 BEGIN TRANSACTION;
 BEGIN TRY

 truncate table dbo.tran1;
 truncate table dbo.tran2;
 INSERT INTO dbo.Tran1
 values ('ost')

 INSERT INTO dbo.tran2
 values('gert')

 END TRY
 BEGIN CATCH
 IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
 THROW;
 END CATCH;
 IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

And if you want it with your error log it looks like this:

BEGIN TRANSACTION;
BEGIN TRY

truncate table dbo.tran1;
truncate table dbo.tran2;


INSERT INTO dbo.tran2
values('frands')
INSERT INTO dbo.Tran1
values ('hans')


END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
INSERT INTO Errors (errornumber, errorseverity, errorstate, errorprocedure, 
errorline, errormessage)
VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), 
ERROR_LINE(), ERROR_MESSAGE())
END CATCH;
IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

Tables

Result

Result with error log