pass multiple values in a single parameter in Informix Stored procedure

hello, Is the following Stored procedure snippet valid in informix the input parameter LoanNo can have values like ('123', '456', '678') CREATE PROCEDURE test_define(LoanNo as char(255)) Returning name as char(50); select first_name into name from employment where Loan_Number in LoanNo return name; end procedure My question is can we pass as an input parameter something like what is mentioned above... I can do this in SQL server... Thanks Anil

beginner wrote: > Is the following Stored procedure snippet valid in informix > > the input parameter LoanNo can have values like ('123', '456', '678') > > CREATE PROCEDURE test_define(LoanNo as char(255)) > Returning name as char(50);> > select first_name into name from employment > where Loan_Number in LoanNo > > return name; > > end procedure > > My question is can we pass as an input parameter something like what > is mentioned above... > > I can do this in SQL server... Yes, and No, but mainly "Yes, but is is not pretty". If you want a list of values passed as a unit, then you have to pass a list -- CREATE PROCEDURE test_define(LoanList LIST{Number CHAR(3)}) RETURNING CHAR(50) AS Name; You might need to specify NOT NULL after the CHAR(3); you can read the manual just as well as I can. I don't have a DBMS to test against where I'm typing this. You could then probably do: DEFINE rv CHAR(50); FOREACH SELECT First_Name INTO rv FROM Employment WHERE Loan_Number IN (SELECT Number FROM TABLE(LoanList)) RETURN rv WITH RESUME; END FOREACH; I freely admit there could be half a dozen syntactical issues to resolve in the SELECT statement; it is tricky stuff and I'm still feeling too lazy to RTF(abulous)M. The calling convention would be: EXECUTE PROCEDURE test_define(LIST{'123','456','678'}::LIST{NumberCHAR(3)}); Again, give or take syntactic peccadilloes. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/