Passing a parameter to IN predicate of DB2 udf

SQL Apprentice Question
I have encountered a problem in passing a VARCHAR parameter (which is a
list of accepted values) to the IN predicate of a DB2 user-defined
function.


For example, I have a table (mytable) storing ppl names & their
corresponding groups. I would like to create a UDF (myfunc) that select
a list of ppl names from (mytable) who belong to some dynamic choices
of groups. I have tried the following codings but it doesn't work if I
try to provide a list of designated choices of groups as a single
parameter.


Can anyone help? Thanks a lot!




////Reference SQLs:


CREATE TABLE mytable (
name VARCHAR(10),
group CHAR(1)
);


INSERT into mytable values ('Peter','A');
INSERT into mytable values ('John','A');
INSERT into mytable values ('Mary','B');
INSERT into mytable values ('Susan','C');


CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(
name VARCHAR(10)
)


LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN


SELECT
name
FROM
mytable
where
group in (cgroup)
;



> db2 "select * from mytable"


NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C

4 record(s) selected.



>db2 "select * from table(myfunc(char('A'))) as t1"


NAME
----------
Peter
John

2 record(s) selected.



>db2 "select * from table(myfunc(char('A,B'))) as t1"


NAME
----------

0 record(s) selected.



>db2 "select * from table(myfunc(char('''A'',''B'''))) as t1"


NAME
----------

0 record(s) selected.



Celko Answers
This is a common Newbie design error, usually comitted by people who
learned to program in BASIC instead of a compiled language. How many
parameters can a proceure accept? I don't the limit in DB2, but SQL
Server can have 1024 of them.

CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(IN foobar_ name VARCHAR(10),
IN p1 INTEGER, IN p2 INTEGER, ..IN pn INTEGER)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECTmfoobar_name
FROM Foobar
WHERE grp_nbr IN (p1, p2, .., pn);


This will avoid dynamic SQL and all the horrible problems you have with
it. You can do this parameter list with a simple text editor.