.

SQL transaction template / skeleton

BEGIN TRANSACTION;
BEGIN TRY
      select * from ......
      update .....
      save TRANSACTION whatever
     
      update ......
    RAISERROR ('now we throw this error as if naturally occured', 18, 10 );
     
      update .....
      COMMIT TRANSACTION; -- it will not reach this code because of forced error
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION whatever;
        -- or ...
            -- IF XACT_STATE() <> -1
           -- ROLLBACK TRANSACTION ProcedureSave;
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();
      --or re raise the error as below
  /*  RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );*/
END CATCH

What's your thoughts on this?

*

Protected by WP Anti Spam