Pass Array as parameter in DB2 Stored Procedure

Question

I am trying to create a stored procedure which takes an array as a parameter and in the WHILE loop iterates through this array and adds the chars into a table.

For example if I had an array of ['a','b','c'] I would want to pass this into my stored procedure and the characters 'a' , 'b' and 'c' to be placed into a table.

My SP creates successfully, but I am having issues when I try to call my procedure. Can anybody point me towards how to pass in an array? My procedure is as follows....

    DROP PROCEDURE DB.LWRH_DYNAMIC_SP@
create type stringArray as VARCHAR(100) array[100]@
CREATE PROCEDURE DB.LWRH_SP
(
    IN list stringArray
)
LANGUAGE SQL
BEGIN
    DECLARE i, MAX INTEGER;
    DECLARE c CHAR(1);
    SET i = 0;
    SET MAX = CARDINALITY(list);
    WHILE i <= MAX DO
    SET c = list[i];
    INSERT INTO schema.test ("SERVICE TYPE")values (c);
    END WHILE;

END@

CALL DB.LWRH_SP('')@

Duplicated question? Read this : [How to pass an array into a SQL Server stored procedure](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — Mateusz Guzdek, Dec 02 '14 at 10:31
@ChestNoot - This is DB2, which is a vastly different product. Things like support for array parameters is spotty. — Clockwork-Muse, Dec 02 '14 at 10:35
@ChestNoot I am using DB2 and i just want to call the procedure using SQL. I am not involving C, Java or any other language. So, I do not think this is a duplicate. — LiamWilson94, Dec 02 '14 at 10:36
I have managed to find a work around for this problem. I used a VARCHAR as the input param and iterated through each CHAR. However, an answer would still be appreciated. Thanks. — LiamWilson94, Dec 02 '14 at 11:08

score 1 · Answer 1 · edited Sep 13 '19 at 09:38

Use the DB2 array constructor to call a stored procedure with array typed input parameters.

An example of creating an array of integers using the array constructor:

ARRAY[1, 2, 3]

For your stored procedure example:

create type stringArray as VARCHAR(100) array[100]@
CREATE PROCEDURE DB.LWRH_SP
(
    IN list stringArray
)
LANGUAGE SQL
BEGIN
    DECLARE i, MAX INTEGER;
    DECLARE c CHAR(1);
    SET i = 0;
    SET MAX = CARDINALITY(list);
    WHILE i <= MAX DO
    SET c = list[i];
    INSERT INTO schema.test ("SERVICE TYPE")values (c);
    END WHILE;
END@

You can call the above stored procedure with an array of VARCHAR using:

CALL DB.LWRH_SP(ARRAY['a', 'b', 'c'])@

NOTE: In my experience some SQL developer tools (eg. DBArtisan) might not work with the above syntax of stored procedure call using the array constructor, but it definitely works with the Linux command line db2 tool aka. DB2 UDB CLP.

score 0 · Answer 2 · answered Dec 02 '14 at 13:59

You need to define the data type before, and then create a variable to pass like parameter.

>>-CREATE--+------------+--PROCEDURE--procedure-name------------>
       '-OR REPLACE-'                              

>--+--------------------------------------------------------------------------------+-->
   '-(--+----------------------------------------------------------------------+--)-'   
        | .-,----------------------------------------------------------------. |        
        | V .-IN----.                                                        | |        
        '---+-------+--parameter-name--| data-type |--+--------------------+-+-'        
            +-OUT---+                                 '-| default-clause |-'            
            '-INOUT-'                                                                   

>--| option-list |--| SQL-procedure-body |---------------------><

data-type

|--+-| built-in-type |---------------+--------------------------|
   +-| anchored-variable-data-type |-+   
   +-array-type-name-----------------+   
   +-cursor-type-name----------------+   
   +-distinct-type-name--------------+   
   '-row-type-name-------------------'

Here, you can see an example of a function that receives an array as parameter. Note the array type was defined before the function. The same is necesary for stored procedures.

--#SET TERMINATOR @
create or replace type my_array_type as varchar(64) array[int]@
create or replace function card (in my_array my_array_type)
  returns int
 begin
  declare card int;
  set card = cardinality(my_array);
  return card;
 end@

create or replace procedure test ()
 begin
  declare size int;
  declare my my_array_type;
  set my [1] = 'uno';
  set my [2] = 'dos';
  set my [3] = 'tres';
  set size = card(my);
  CALL DBMS_OUTPUT.PUT('Cardinality = ');
  CALL DBMS_OUTPUT.PUT_LINE(size);
 end@

SET SERVEROUTPUT ON@

call test ()@

Remember that an array is different to a string (CHAR). The arrays are an internal object in DB2, and they need to be defined as variable before use them. Strings can be created as you did: ''. However, they are two different things in DB2.

Pass Array as parameter in DB2 Stored Procedure

2 Answers2

Linked