T-SQL transaction syntax

Question

I am executing a critical stored procedure that will execute UPDATE , DELETE and INSERT , and I want my TRANSACTION be properly formed.

I saw several TRANSACTION statements where there is a check after each step. I also saw this view, where the entire set of steps is simply placed in one TRANSACTION block without any “control points” along the way.

Is it a well-formed TRANSACTION that rolls back everything, i.e. UPDATE , DELETE and INSERT if there is any error at any point.

Here's the TRANSACTION :

 BEGIN TRANSACTION BEGIN TRY UPDATE SomeTable SET SomeColumnValue = 123 WHERE Id = 123456 DELETE FROM SomeOtherTable WHERE Id = 789 INSERT INTO ThirdTable (Column1, Column2) VALUE ('Hello World', 1234567) END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH 

Answer 1

You can use the syntax as shown below. Note that the syntax also takes into account a nested transaction when another SP with a similar structure is called from inside the try begin block.

 BEGIN TRAN BEGIN TRY UPDATE SomeTable SET SomeColumnValue = 123 WHERE Id = 123456 DELETE FROM SomeOtherTable WHERE Id = 789 INSERT INTO ThirdTable (Column1, Column2) VALUE ('Hello World', 1234567) COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; INSERT INTO LogError ( --ErrorID objectName ,ErrorCode ,ErrorDescription ,ErrorGenerationTime ) SELECT -- autogenerated OBJECT_NAME(@@PROCID) ,ERROR_NUMBER() AS ErrorCode ,'Error of Severity: ' + CAST (ERROR_SEVERITY() AS VARCHAR (4)) +' and State: ' + CAST (ERROR_STATE() AS VARCHAR (8)) +' occured in Line: ' + CAST (ERROR_LINE() AS VARCHAR (10)) +' with following Message: ' + ERROR_MESSAGE() AS ErrorColumnDescription ,GETDATE() END CATCH 

Answer 2

In the try block, you need to call COMMIT TRANSACTION . This will be the last statement in the try block. So if all goes well, then it will be done differently, it will be rolled back in the catch block.