Evo - SQL - Delete by audit number.

CREATE PROCEDURE dbo._as_DeleteByAuditNumber
    @cAuditNumber NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    --------------------------------------------------
    -- 1) Ensure log table exists
    IF OBJECT_ID('dbo._as_DeleteLog','U') IS NULL
    BEGIN
        CREATE TABLE dbo._as_DeleteLog
        (
            LogID          INT IDENTITY PRIMARY KEY,
            cAuditNumber   NVARCHAR(100)    NOT NULL,
            PostGL         NVARCHAR(MAX)    NULL,
            PostAR         NVARCHAR(MAX)    NULL,
            PostAP         NVARCHAR(MAX)    NULL,
            ErrorOccurred  BIT              NOT NULL DEFAULT(0),
            ErrorMessage   NVARCHAR(MAX)    NULL,
            CreatedDate    DATETIME         NOT NULL DEFAULT(GETDATE())
        );
    END

    --------------------------------------------------
    -- 2) Start transaction
    BEGIN TRAN;
    BEGIN TRY

        --------------------------------------------------
        -- 3) Blocking checks
        IF EXISTS (
            SELECT 1
            FROM dbo.PostAR
            WHERE cAuditNumber = @cAuditNumber
              AND InvNumKey > 0
        )
            THROW 50001, 'Cannot delete: matching PostAR records have InvNumKey > 0.', 1;

        IF EXISTS (
            SELECT 1
            FROM dbo.PostAP
            WHERE cAuditNumber = @cAuditNumber
              AND InvNumKey > 0
        )
            THROW 50002, 'Cannot delete: matching PostAP records have InvNumKey > 0.', 1;

        IF EXISTS (
            SELECT 1
            FROM dbo.PostST
            WHERE cAuditNumber = @cAuditNumber
        )
            THROW 50003, 'Cannot delete: audit number exists in PostST.', 1;

        --------------------------------------------------
        -- 4) Capture JSON of to-be-deleted rows
        DECLARE
            @jsonGL NVARCHAR(MAX),
            @jsonAR NVARCHAR(MAX),
            @jsonAP NVARCHAR(MAX);

        SELECT @jsonGL = (
            SELECT *
            FROM dbo.PostGL
            WHERE cAuditNumber = @cAuditNumber
            FOR JSON AUTO, INCLUDE_NULL_VALUES
        );

        SELECT @jsonAR = (
            SELECT *
            FROM dbo.PostAR
            WHERE cAuditNumber = @cAuditNumber
            FOR JSON AUTO, INCLUDE_NULL_VALUES
        );

        SELECT @jsonAP = (
            SELECT *
            FROM dbo.PostAP
            WHERE cAuditNumber = @cAuditNumber
            FOR JSON AUTO, INCLUDE_NULL_VALUES
        );

        --------------------------------------------------
        -- 5) Perform deletes
        DELETE FROM dbo.PostGL
         WHERE cAuditNumber = @cAuditNumber;

        DELETE FROM dbo.PostAR
         WHERE cAuditNumber = @cAuditNumber;

        DELETE FROM dbo.PostAP
         WHERE cAuditNumber = @cAuditNumber;

        --------------------------------------------------
        -- 6) Log successful deletion
        INSERT INTO dbo._as_DeleteLog
            (cAuditNumber, PostGL, PostAR, PostAP, ErrorOccurred)
        VALUES
            (@cAuditNumber, @jsonGL, @jsonAR, @jsonAP, 0);

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        --------------------------------------------------
        -- 7) Rollback and log error
        IF XACT_STATE() <> 0
            ROLLBACK TRAN;

        DECLARE
            @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE();

        INSERT INTO dbo._as_DeleteLog
            (cAuditNumber, ErrorOccurred, ErrorMessage)
        VALUES
            (@cAuditNumber, 1, @ErrMsg);

        -- Re-raise so caller sees the error
        THROW;
    END CATCH
END
GO