helper procedure to write data into DI – mainly for bulks consisting of more than one EcENTITY row
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