Help Please - Executing a SQL statement in a variable.

I have a basic Informix question that hopefully someone can help me with. We have a stored procedure that is supposed to create a "Search" query. The stored procedure has a variable passed to it containing the search keywords that the user types in. The procedure breaks these keywords down to create a SQL statement that reads something like this: Select rec_id from mytable where searchdata matches "*word1*" ormatches "*word2*"...etc The problem is that we can't execute a "Select" statement directly because we do not know how many keywords the user is going to enter. So I was thinking of building the select statement into a variable and then at the end executing it...problem is, I don't know the commands to do this. I can build the select statement ok...that is, I have a variable called mysql that contains the "Select rec_id from...". But how do I execute this so that the correct result set is returned? Appologies is this is a stupid question...I'm just not that familiar with Informix yet. Thanks for any assistance. Brent.

Brent wrote: > We have a stored procedure [...] > The problem is that we can't execute a "Select" statement directly > because we do not know how many keywords the user is going to enter. > So I was thinking of building the select statement into a variable and > then at the end executing it...problem is, I don't know the commands > to do this. > > I can build the select statement ok...that is, I have a variable > called mysql that contains the "Select rec_id from...". But how do I > execute this so that the correct result set is returned? > > Appologies is this is a stupid question...I'm just not that familiar > with Informix yet. Thanks for any assistance. It's a fairly FAQ, or at least minor variants of it are frequently asked. The shortest answer is "SPL does not support dynamic SQL", and what you need is dynamic SQL. The next shortest answer is "There is the EXEC bladelet that is available from the IIUG (http://www.iiug.org/software) that can be installed in an IDS 9.x server to give you support for dynamic SQL in SPL". -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<3EB5F8D5.4010701@earthlink.net>... > Brent wrote: > > We have a stored procedure [...] > > > It's a fairly FAQ, or at least minor variants of it are frequently asked. > > The shortest answer is "SPL does not support dynamic SQL", and what > you need is dynamic SQL. > > The next shortest answer is "There is the EXEC bladelet that is > available from the IIUG (http://www.iiug.org/software) that can be > installed in an IDS 9.x server to give you support for dynamic SQL in > SPL". Thank you for your help. I appreciate it. Brent.