String parm in Stored Proc. & using it with IN clause?

Here is the situation I am trying to resolve: I have an Integer type column say prod_no and I am passing a string with values say "2344, 5678, 98" through the <as_prod_nos> parameter. I have a Select statement inside the Stored Proc. that says <FOREACH SELECT prod_name into s_prod_name FROM Products WHERE prod_no IN (as_prod_nos) .. END FOREACH;> On excuting I get a data type conversion error (-1213). How can I avoid this? I will appreciate any help. If you know if I can use PREPARE and then execute in a stored procedure then please let me know that too. Thanks, Manas * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Manas <manasNOmaSPAM@hotmail.com.invalid> wrote in message news:097f1f94.de7972c4@usw-ex0107-049.remarq.com... > Here is the situation I am trying to resolve: > > I have an Integer type column say prod_no and I am > passing a string with values say "2344, 5678, 98" through the > <as_prod_nos> parameter. > > I have a Select statement inside the Stored Proc. > that says > > <FOREACH SELECT prod_name into s_prod_name FROM > Products WHERE prod_no IN (as_prod_nos) > .. > > END FOREACH;> > > On excuting I get a data type conversion error (-1213). > > How can I avoid this? > This is quick and dirty, will perform a sequential scan, and will fail if you do not pass the string formated the right way. If you make sure that you pass a string that looks like this ",2344, 5678, 98," then you can use FOREACH SELECT prod_name into s_prod_name FROM Products where as_prod_nos matches '*,' || prod_no || ',*' > I will appreciate any help. If you know if I can use PREPARE and then > execute in a stored procedure then please let me know that too. > > Thanks, > Manas > > > * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * > The fastest and easiest way to search and participate in Usenet - Free! >