DB2 Stored Procedure Return ID's

Question

I would like to have a stored procedure to create several records and return the IDs as an array, preferably as a single column result set. So far I am able to create the items from a single input parameter json, and return a hard coded integer, I am missing the bit that gets the IDS and sends them back. I can get the ID using the FINAL TABLE method, really its just how to do the array of ID's bit.

Heres what I have at the moment

CREATE OR REPLACE PROCEDURE INSERT_ITEMS (IN JSON_VALUE_IN CLOB(16777216))
LANGUAGE SQL
MODIFIES SQL DATA

BEGIN
DECLARE intEventId BIGINT;


DECLARE C2 CURSOR WITH RETURN FOR SELECT intEventId AS EVENTID FROM (VALUES(intEventId));

    INSERT INTO ITEMS (
        TITLE
    )
    SELECT
        JSON_VAL(SYSTOOLS.JSON2BSON(ITEMS.value), 'title', 's:100')
    
    FROM
        TABLE(
            SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(JSON_VALUE_IN), 'items', 's:32000')
        ) ITEMS
    SET intEventId = 1;
    
OPEN C2;

    
END
@

Answer 1

Try this:

CREATE TABLE ITEMS 
(
  ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY
, TITLE VARCHAR (100)
)@

CREATE OR REPLACE PROCEDURE INSERT_ITEMS (IN JSON_VALUE_IN CLOB(16777216))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
MODIFIES SQL DATA
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR  
  WITH T AS
  (
    -- You get a list of generated IDs with this SELECT
    SELECT ID
    FROM
    NEW TABLE
    (
      INSERT INTO ITEMS (TITLE)
      SELECT VALUE
      -- SP input is converted to a string representation of a JSON document;
      -- JSON document is passed to the JSON_TABLE function
      -- which tokenizes the input: you get N rows with 1 title each
      FROM TABLE (SYSTOOLS.JSON_TABLE (SYSTOOLS.JSON2BSON ('{"e":[' || JSON_VALUE_IN || ']}'), 'e', 's:100')) T
    )
  )
  -- The list of generated IDs is converted to a JSON array of these IDs;
  -- The output has 1 row and 1 column
  VALUES JSON_ARRAY ((SELECT ID FROM T) FORMAT JSON);

  OPEN C1;
END
@

CALL INSERT_ITEMS ('"Title1", "Title2", "Title3"')@

|1      |
|-------|
|[1,2,3]|

SELECT * FROM ITEMS@

|ID |TITLE |
|---|------|
|1  |Title1|
|2  |Title2|
|3  |Title3|