CODEWITHSUNDEEP
databasestored-proceduresdb2
unknown
unknown

Reputation: 689

how to pass input parameter as a string separated by comma or list for IN clause in DB2

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.

Upvotes: 2

Views: 2132

Answers (2)

WarrenT
WarrenT

Reputation: 4532

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.

Upvotes: 1

valex
valex

Reputation: 24134

Something like this:

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

Upvotes: 0

Related Questions