Stored procedure EcWriteEntity

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

The source code:

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

Last updated