StoredProcedureWithError

USE [example_database]
GO
/****** Object:  StoredProcedure [dbo].[test_error_cannot_insert_null]    Script Date: 2/6/2019 1:59:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Gary Reckard
-- Create date: 2019-06-19
-- Description: testing error reporting from calling procedures
-- =============================================
ALTER PROCEDURE [dbo].[test_error_cannot_insert_null]
AS
BEGIN

    DECLARE @TranCounter INT;
    SET @TranCounter = @@TRANCOUNT;
    IF @TranCounter > 0
        SAVE TRANSACTION ProcedureSave;
    ELSE
        BEGIN TRANSACTION;

    SET XACT_ABORT ON;

    BEGIN TRY

        --------------------------------------------------
        -------------///// BEGIN SQL \\\\\----------------
        --------------------------------------------------

            --this should fail!
            INSERT INTO [roles]
                (
                    name,
                    description
                )
            VALUES
                (
                    null,
                    null
                );

        --------------------------------------------------
        --------------\\\\\ END SQL /////-----------------
        --------------------------------------------------


        IF @TranCounter = 0
            COMMIT TRANSACTION;


    END TRY
    BEGIN CATCH
        DECLARE @errorProcedure varchar(100) = ERROR_PROCEDURE(),
                @errorShort varchar(255)     = ERROR_MESSAGE(),
                @errorCode varchar(50)       = ERROR_NUMBER(),
                @errorLine int               = ERROR_LINE(),
                @errorSeverity int           = ERROR_SEVERITY(),
                @errorState int              = ERROR_STATE(),
                @xstate int                  = XACT_STATE();

        if @xstate = -1                      ROLLBACK TRANSACTION;
        if @xstate = 1 and @TranCounter = 0  ROLLBACK TRANSACTION;
        if @xstate = 1 and @TranCounter > 0  ROLLBACK TRANSACTION ProcedureSave;

        -- Log errors
        EXEC errors_add @errorCode, @errorProcedure,  @errorShort, NULL, @errorLine, @errorSeverity, @errorState, @xstate;

        THROW;
        RETURN;
    END CATCH

END