how to pass input parameter as a string separated

Question

I am new in stored procedure ,i have one doubt,I need to pass input parameter as a string separated by comma or list for IN clause in DB2. please see the below sample procedure

 CREATE PROCEDURE  TEST_SP(IN  @listofUsername)
  SPECIFIC TEST_SP DYNAMIC RESULT SETS 1
P1:BEGIN
  DECLARE CURSOR1 CURSOR WITH RETURN FOR  
  SELECT  F_NAME FROM TEST  WHERE USER_NAME IN (@listofusername);
    }
    OPEN CURSOR1;  
END P1 

Please guide me, 1 how to write the above stored procedure 2 Is it possible to pass any kind of list as a input parameter ,if yes , which one will give better performance.

Answer 1

Something like this:

Select f_name from test1 
where CONCAT(',',@listofusername,',')  LIKE CONCAT('%,',username,',%')

Answer 2

You can create an array datatype, and define your parameter as that datatype.

CREATE OR REPLACE TYPE UserList as char(10) ARRAY[100]

I picked char(10) because that's what they are on my system, IBM i. Use whatever is appropriate for yours, and an array size large enough for whatever use you may have.

Use this as the type for your procedure parameter.