Stored Procedure Question
Hi all,
I need some advice on a function which is still in the testing stages.
The function dynamically creates a stored procedure with an unique name,
executes the procedure and then drops it. Because the select statement
(columns being selected, where clause and table name) are not known until
the function is executed, I am unable to program in 4gl the function to
take care of all this. My questions are:
1. Is this practice acceptable?
2. Will this cause problems with the engine (SE 7.1 and On-line 7.20)?
3. If this does cause problems does anyone have a suggestion on doing
the same in 4gl?
Here is what it looks like in case some of you need more info.
The 4gl code:
let Mproc="create procedure ",Muser clipped,"() returning char(80);",
"define ",Mdefine clipped,"define Mi smallint;define Mret char(80);",
"define Mblank char(80);let Mblank=","' ';",
"let Mi=1;foreach ",Mselect clipped,Mnull clipped," let Mret=",Mlet clipped,
";return Mret with resume;let Mi=Mi+1;if Mi>100 then exit foreach;end if ",
"end foreach end procedure"
Example of the procedure it creates. -formated for readability
create procedure joseph() returning char(80);define Mlast_dt_tm,Mlname,Mfname,Mmid_init,Mssn,Mapp_stat char(80);
define Mi smallint;
define Mret char(80);
define Mblank char(80);
let Mblank=' ';
let Mi=1;
foreach select last_dt_tm,lname,fname,mid_init,ssn,app_stat into
Mlast_dt_tm,Mlname,Mfname,Mmid_init,Mssn,Mapp_stat from app_driver where
lname<'B' order by lname,fname,ssn
if Mlast_dt_tm is null then
let Mlast_dt_tm=Mblank; end if
if Mlname is null then
let Mlname=Mblank; end if
if Mfname is null then
let Mfname=Mblank; end if
if Mmid_init is null then
let Mmid_init=Mblank; end if
if Mssn is null then
let Mssn=Mblank; end if
if Mapp_stat is null then
let Mapp_stat=Mblank; end if
let Mret=Mlast_dt_tm[1, 17]||Mlname[1, 26]||Mfname[1, 16]||
Mmid_init[1, 2]||Mssn[1, 12]||Mapp_stat[1, 3];
return Mret with resume;
let Mi=Mi+1;
if Mi>100
then
exit foreach;
end if
end foreach end procedure
Thanks for your help.
--
Joseph Cullipher |E-mail: joseph@cannonexpress.com
PO Box 364 |opinions express are those of my own and
Springdale, AR 72764 USA |don't necessarily reflect those of my company