SQL TRY-CATCH WITH TRANSACTION

trycatchtran.sql

CREATE PROCEDURE [dbo].[spTxnDemo]
  @debug int = NULL
  , @forceError int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET @debug = ISNULL(@debug, 0);
    SET @forceError = ISNULL(@forceError, 0);
    BEGIN TRY
        BEGIN TRANSACTION MY_TXN;



        IF (ISNULL(@forceError, 0) > 0)
            SELECT 1/0  -- Generates divide by zero error causing control to jump into catch

        IF @debug > 0
          PRINT '>> COMMITTING'
        COMMIT TRANSACTION MY_TXN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            IF @debug > 0
              PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MY_TXN;
        END;
        
        THROW;
    END CATCH
END