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

Manas wrote: >> >> 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). On our 7.31 this doesn't error out, but it doesn't do what want. It only tests against the first numeric element of the string. Hardcoding 'as_prod_nos' in the test, still quote delimited, concat the number and then does the test. >> >> How can I avoid this? > >Stop trying to sneak dynamic SQL into stored procedures. SPL does NOT >support dynamic SQL in any form! Package that SQL into a library >function in C and call it from there. It will be much faster anyway. If you get the little blade published the other day then you can extend SPL to allow dynamic SQL. However you do need the later engines and the blade does exactly what it says on the tin. > >> 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. > >No you cannot.