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