Stored procedure EcWriteEntity

helper procedure to write data into DI – mainly for bulks consisting of more than one EcENTITY row

parameter

type

direction

description

@EntName

nvarchar (100) Required

Input

Entity name – for description, see Table EcENTITY, field β€˜name’

@BulkId

int Optional

InputOutput

Transfer request bulk_id – see remarks under Table EcENTITY description

  • use NULL value to create a first EcENTITY row;

the procedure creates a new bulk_id value and passes it back

  • for writing next EcENTITY rows (within one bulk) use the value which this procedure returned in this parameter when writing the first EcENTITY row

@EnClassID

nvarchar(10)

Optional

Input

Entity type

if omitted, β€˜ITEM’ value is used

RetValue

bigint

ReturnValue

EcENTITY.id inserted

The source code:

-- =============================================

-- Author: Bronislav SIMUNEK

-- Create date: 2016-07-01

-- Description: Insert a data transfer request

-- =============================================

CREATE PROCEDURE [dbo].[EcWriteEntity]
    @EntName NVARCHAR (100),
    @BulkID INT = -1 OUT,
    @EntClassID NVARCHAR (10) = 'ITEM'
AS
BEGIN
    -- check mandatory fields:
    IF @EntClassID IS NULL
    BEGIN
        RAISERROR('@EntClassID parameter is NULL', 16, 1);
        RETURN -1;
    END
    IF @EntName IS NULL
    BEGIN
        RAISERROR('@EntName parameter is NULL', 16, 1);
        RETURN -1;
    END
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements:
    SET NOCOUNT ON;
    DECLARE @InsertedID BIGINT;
    DECLARE @result TABLE ( bid int, rid bigint );
    IF @BulkID IS NULL OR @BulkID < 0
    BEGIN
        INSERT INTO EcENTITY
        (ent_class_id, name, comm_dir, bulk_id)
        OUTPUT INSERTED.bulk_id, INSERTED.id INTO @result
        VALUES
        ( @EntClassID, @EntName, 1, ISNULL((SELECT MAX(bulk_id) FROM EcENTITY), 0) + 1);
    END
    ELSE
    BEGIN
        INSERT INTO EcENTITY
        (ent_class_id, name, comm_dir, bulk_id)
        OUTPUT INSERTED.bulk_id, INSERTED.ID INTO @result
        VALUES
        ( @EntClassID, @EntName, 1, @BulkID);
    END
    
    SET @BulkID = (SELECT TOP 1 bid FROM @result);
    SET @InsertedID = (SELECT TOP 1 rid FROM @result);
    RETURN @InsertedID;
END