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