SPL procedure passing table name

Is there a trick to passing a table name as a paramter to a SPL procedure or function so it can be used in select, delete, or update statements? I would like to create a stored procedure that passess a table name and have the stored procedure act on that table (add or delete rows) as needed. Normally I would embed the tablename in the procedure if it was specific to one table, but we have several tables that could be acted on by this same procedure if a table name could be passed. I have tried code similar to the following but I get an error indicating that table "tbname" doesn't exist. create procedure drop_data (tbname, pct) delete from tbname where tbname.percent < pct;end procedure; Thanks! Jim Penix

Jim Penix wrote: > Is there a trick to passing a table name as a paramter to a SPL procedure or > function so it can be used in select, delete, or update statements? > > I would like to create a stored procedure that passess a table name and have > the stored procedure act on that table (add or delete rows) as needed. > Normally I would embed the tablename in the procedure if it was specific to > one table, but we have several tables that could be acted on by this same > procedure if a table name could be passed. > > I have tried code similar to the following but I get an error indicating > that table "tbname" doesn't exist. > > create procedure drop_data (tbname, pct) > delete from tbname > where tbname.percent < pct;> end procedure; > The short answer is "You cannot do Dynamic SQL with SPL", and that is dynamic SQL. A longer answer points you to the recent message from Paul Brown inviting you to use some UDR's which implement Dynamic SQL for use in stored procedures. Obviously, using that requires a 9.x Informix database. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

If you've got the 9 engine download his UDR that will allow this (it is very good), otherwise you are stuffed Paul Jim Penix wrote: > > Is there a trick to passing a table name as a paramter to a SPL procedure or > function so it can be used in select, delete, or update statements? > > I would like to create a stored procedure that passess a table name and have > the stored procedure act on that table (add or delete rows) as needed. > Normally I would embed the tablename in the procedure if it was specific to > one table, but we have several tables that could be acted on by this same > procedure if a table name could be passed. > > I have tried code similar to the following but I get an error indicating > that table "tbname" doesn't exist. > > create procedure drop_data (tbname, pct) > delete from tbname > where tbname.percent < pct;> end procedure; > > Thanks! > > Jim Penix -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #