Re: Tabname as a parameter in a stored procedure

This may be the second most FAQ and should probably be in the FAQ file. No you cannot do what you wish to do directly, SPL does not support dynamic SQL. There is a kludgy workaround. The script can execute a CREATE PROCEDURE statement or SYSTEM an external program of script to do the same, and create a stored procedure to perform the intended query, execute it in a CURSOR in a FOREACH loop and return its results. After the loop the outer procedure can drop the temporary procedure. There are potential problems with this method like name clashes if two users call the outer proc at the same time, however. This is like scratching your head with your toe but it gets the job done. Art S. Kagel fabioantunes@my-deja.com wrote: > > Is there any way to perform a select inside an stored procedure wich > name is passed as a parameter ??? > > Like in the example above: > > create procedure select_tab(tabname char(8))> > select * from tabname > where 1=1;> > end procedure; > > Thanks in advance > > -- > Fábio Antunes > Fanix Consultoria Ltda. > Brasil - SP > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <387353D8.C3E0392B@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >This may be the second most FAQ and should probably be in the FAQ file. > >No you cannot do what you wish to do directly, SPL does not support dynamic >SQL. There is a kludgy workaround. The script can execute a CREATE >PROCEDURE statement or SYSTEM an external program of script to do the same, >and create a stored procedure to perform the intended query, execute it in >a CURSOR in a FOREACH loop and return its results. After the loop the outer >procedure can drop the temporary procedure. There are potential problems >with this method like name clashes if two users call the outer proc at the >same time, however. This is like scratching your head with your toe but it >gets the job done. > Added to TODO list for the FAQ. Look for new version this weekend! >Art S. Kagel > >fabioantunes@my-deja.com wrote: >> >> Is there any way to perform a select inside an stored procedure wich >> name is passed as a parameter ??? >> >> Like in the example above: >> >> create procedure select_tab(tabname char(8))>> >> select * from tabname >> where 1=1;>> >> end procedure; >> >> Thanks in advance >> >> -- >> Fábio Antunes >> Fanix Consultoria Ltda. >> Brasil - SP >> >> Sent via Deja.com http://www.deja.com/ >> Before you buy. -- David Williams