TransactionWithRollbackOnError.sql

BEGIN TRY
    BEGIN TRANSACTION

    -- statements here

    COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

    PRINT 'Actual error number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(ERROR_LINE() AS VARCHAR(10));

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH