Skriptentwickung/current/[DD_IIM]-Database/[TBDD_CFG_FUNCTION_MODULE_PROCEDURES].sql

/* ============================================================================
   FUNCTION_MODULE – PROZEDUREN
   Voraussetzung: Tabelle [_meta].[TBDD_CFG_FUNCTION_MODULE] existiert
   ============================================================================ */

USE [DD_IIM];
GO

CREATE OR ALTER PROCEDURE [_meta].[PRDD_UPSERT_FUNCTION_MODULE]
    @PK_CFG_FUNCTION_MODULE_ID BIGINT = NULL,
    @ACTIVE BIT,
    @INTERNAL BIT,
    @FUNCTION_NAME NVARCHAR(50),
    @FUNCTION_TYPE NVARCHAR(10),
    @FUNCTION_DEFINITION NVARCHAR(MAX),
    @COMMENT NVARCHAR(500) = NULL,
    @VERSION SMALLINT = 1,
    @CREATED_WHO BIGINT,
    @CREATED_WHEN DATETIME2(0),
    @CHANGED_WHO BIGINT = NULL,
    @CHANGED_WHEN DATETIME2(0) = NULL,
    @OUT_PK_CFG_FUNCTION_MODULE_ID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @identity_insert_enabled BIT = 0;
    DECLARE @next_insert_id BIGINT;
    DECLARE @app_lock_result INT;

    BEGIN TRY
        SET CONTEXT_INFO 0x544244445F46554E4354494F4E5F4D4F44554C45;

        IF @PK_CFG_FUNCTION_MODULE_ID IS NOT NULL
        BEGIN
            UPDATE [_meta].[TBDD_CFG_FUNCTION_MODULE]
               SET [ACTIVE] = @ACTIVE,
                   [INTERNAL] = @INTERNAL,
                   [FUNCTION_NAME] = @FUNCTION_NAME,
                   [FUNCTION_TYPE] = @FUNCTION_TYPE,
                   [FUNCTION_DEFINITION] = @FUNCTION_DEFINITION,
                   [COMMENT] = @COMMENT,
                   [VERSION] = @VERSION,
                   [CHANGED_WHO] = @CHANGED_WHO,
                   [CHANGED_WHEN] = @CHANGED_WHEN
             WHERE [PK_CFG_FUNCTION_MODULE_ID] = @PK_CFG_FUNCTION_MODULE_ID;

            IF @@ROWCOUNT = 1
            BEGIN
                SET @OUT_PK_CFG_FUNCTION_MODULE_ID = @PK_CFG_FUNCTION_MODULE_ID;
            END
            ELSE
            BEGIN
                IF @PK_CFG_FUNCTION_MODULE_ID < 100001
                BEGIN
                    THROW 52011, 'Explizite PK_CFG_FUNCTION_MODULE_ID muss >= 100001 sein.', 1;
                END;

                SET @identity_insert_enabled = 1;
                SET IDENTITY_INSERT [_meta].[TBDD_CFG_FUNCTION_MODULE] ON;

                INSERT INTO [_meta].[TBDD_CFG_FUNCTION_MODULE]
                (
                    [PK_CFG_FUNCTION_MODULE_ID],
                    [ACTIVE],
                    [INTERNAL],
                    [FUNCTION_NAME],
                    [FUNCTION_TYPE],
                    [FUNCTION_DEFINITION],
                    [COMMENT],
                    [VERSION],
                    [CREATED_WHO],
                    [CREATED_WHEN],
                    [CHANGED_WHO],
                    [CHANGED_WHEN]
                )
                VALUES
                (
                    @PK_CFG_FUNCTION_MODULE_ID,
                    @ACTIVE,
                    0,
                    @FUNCTION_NAME,
                    @FUNCTION_TYPE,
                    @FUNCTION_DEFINITION,
                    @COMMENT,
                    @VERSION,
                    @CREATED_WHO,
                    @CREATED_WHEN,
                    @CHANGED_WHO,
                    @CHANGED_WHEN
                );

                SET IDENTITY_INSERT [_meta].[TBDD_CFG_FUNCTION_MODULE] OFF;
                SET @identity_insert_enabled = 0;

                SET @OUT_PK_CFG_FUNCTION_MODULE_ID = @PK_CFG_FUNCTION_MODULE_ID;
            END;
        END
        ELSE
        BEGIN
            EXEC @app_lock_result = sys.sp_getapplock
                @Resource = N'_meta.PRDD_UPSERT_FUNCTION_MODULE.IDENTITY_RANGE',
                @LockMode = N'Exclusive',
                @LockOwner = N'Session',
                @LockTimeout = 10000;

            IF @app_lock_result < 0
            BEGIN
                THROW 52010, 'Konnte keine exklusive ID-Sperre für [_meta].[TBDD_CFG_FUNCTION_MODULE] erhalten.', 1;
            END;

            SELECT @next_insert_id = CASE
                WHEN MAX([PK_CFG_FUNCTION_MODULE_ID]) IS NULL OR MAX([PK_CFG_FUNCTION_MODULE_ID]) < 100000 THEN 100001
                ELSE MAX([PK_CFG_FUNCTION_MODULE_ID]) + 1
            END
            FROM [_meta].[TBDD_CFG_FUNCTION_MODULE];

            SET @identity_insert_enabled = 1;
            SET IDENTITY_INSERT [_meta].[TBDD_CFG_FUNCTION_MODULE] ON;

            INSERT INTO [_meta].[TBDD_CFG_FUNCTION_MODULE]
            (
                [PK_CFG_FUNCTION_MODULE_ID],
                [ACTIVE],
                [INTERNAL],
                [FUNCTION_NAME],
                [FUNCTION_TYPE],
                [FUNCTION_DEFINITION],
                [COMMENT],
                [VERSION],
                [CREATED_WHO],
                [CREATED_WHEN],
                [CHANGED_WHO],
                [CHANGED_WHEN]
            )
            VALUES
            (
                @next_insert_id,
                @ACTIVE,
                0,
                @FUNCTION_NAME,
                @FUNCTION_TYPE,
                @FUNCTION_DEFINITION,
                @COMMENT,
                @VERSION,
                @CREATED_WHO,
                @CREATED_WHEN,
                @CHANGED_WHO,
                @CHANGED_WHEN
            );

            SET IDENTITY_INSERT [_meta].[TBDD_CFG_FUNCTION_MODULE] OFF;
            SET @identity_insert_enabled = 0;

            EXEC sys.sp_releaseapplock
                @Resource = N'_meta.PRDD_UPSERT_FUNCTION_MODULE.IDENTITY_RANGE',
                @LockOwner = N'Session';

            SET @OUT_PK_CFG_FUNCTION_MODULE_ID = @next_insert_id;
        END;

        SET CONTEXT_INFO 0x00;
    END TRY
    BEGIN CATCH
        IF @identity_insert_enabled = 1
        BEGIN
            BEGIN TRY
                SET IDENTITY_INSERT [_meta].[TBDD_CFG_FUNCTION_MODULE] OFF;
            END TRY
            BEGIN CATCH
            END CATCH
        END;

        BEGIN TRY
            EXEC sys.sp_releaseapplock
                @Resource = N'_meta.PRDD_UPSERT_FUNCTION_MODULE.IDENTITY_RANGE',
                @LockOwner = N'Session';
        END TRY
        BEGIN CATCH
        END CATCH

        SET CONTEXT_INFO 0x00;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE [_meta].[PRDD_DELETE_FUNCTION_MODULE]
    @PK_CFG_FUNCTION_MODULE_ID BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SET CONTEXT_INFO 0x544244445F46554E4354494F4E5F4D4F44554C45;

        DELETE FROM [_meta].[TBDD_CFG_FUNCTION_MODULE]
         WHERE [PK_CFG_FUNCTION_MODULE_ID] = @PK_CFG_FUNCTION_MODULE_ID;

        SET CONTEXT_INFO 0x00;
    END TRY
    BEGIN CATCH
        SET CONTEXT_INFO 0x00;
        THROW;
    END CATCH
END;
GO