CODEWITHSUNDEEP
sqlstored-proceduresdb2
LiamWilson94
LiamWilson94

Reputation: 458

Pass Array as parameter in DB2 Stored Procedure

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('')@

Upvotes: 1

Views: 16879

Answers (3)

user24819440
user24819440

Reputation: 11

I don't have the reputation points to add a comment to mocha's answer, but when trying it out, I found that DB2's array index starts at 1 rather than 0 (need Set i = 1;). Also, the while loop needs to increment i (need: SET i = i +1;) After that works great, thank you!

Upvotes: 1

mocha
mocha

Reputation: 11

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.

Upvotes: 1

AngocA
AngocA

Reputation: 7693

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.

Upvotes: 0

Related Questions