T-SQL Transaction syntax

Question

I'm implementing a mission critical stored procedure that will perform UPDATE, DELETE and INSERT and I want to make sure my TRANSACTION is correctly formed.

I've seen some TRANSACTION statements where there's a check after each step. I've also seen this kind where the entire set of steps are simply placed within a singleTRANSACTION block without any "check points" along the way.

Is this a well formed TRANSACTION that will roll back everything i.e. UPDATE, DELETE and INSERT, if there's 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 a syntax like below. Note that ths syntax also takes care of nested transaction when a another SP with similar structure is called from inside the begin try 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

You need to call COMMIT TRANSACTION within try block. It will be the last statement in try block. So that if everything is goes well then it will be committed else it will be rolled back in catch block.