RE: Dynamic SQL in SPL
In article <6udiet$phi$1@news.xmission.com>, NGuffey@jdriscoll.com (Nicole
Guffey) wrote:
>
> >In article <6ub6rv$f2t$1@news.xmission.com>,
NGuffey@jdriscoll.com
> (Nicole
> >Guffey) wrote:
>
> > >
> > > Does anyone know of a way to use Dynamic SQL or Macro Expansion
> > > within
> > > Stored Procedure Language? I need to be able to pass all or part
> > > (say
> > > only
> > > the where clause) of a SQL Statement as a parameter of type varchar
> > > to a
> > > stored proc and have it parsed and executed as a SQL Statement.
> > >
> > > Thank you,
> > > Nicole Guffey
> > > DBA
> > > J. Driscoll & Associates
> > > nguffey@jdriscoll.com
> > >
> > >
> >You can't do this, but you can dynamically create SPL and then
> execute it
> >from inside existing SPL
>
> >Paul Watson # I don't suffer from
> >WF Software Ltd. # stress, I'm just
> >Tel. (+44) 1436 674729 # a carrier
> >Fax. (+44) 1436 678693 #
> >www.wfsoftware.co.uk
>
> I'm not sure what you mean, could you clarify?
>
> Thank you,
> Nicole Guffey
> DBA
> J.Driscoll & Associates
> nguffey@jdriscoll.com
>
>
Something long the lines of
create spl_dynanmic(p_where clause)
l_spstart="create spl etc"
l_sqlstr = "select * from table" || p_where
l_endsp="end procedure etc"
system "dbaccess "||l_spstart||l_sqlstr||l_endsp;
end procedure
execute procedure spl_create("where col = 1234");
execute procedure spl_dynamic
I know its rough but you get the idea. The other alternative is just to
pass the where clause via a system command to a script. I prefer the
later 'cos it offers much greater functionality. You need to take care
with transaction boundaries and locking sysprocplan though
Hope it helps
Paul Watson # I don't suffer from
WF Software Ltd. # stress, I'm just
Tel. (+44) 1436 674729 # a carrier
Fax. (+44) 1436 678693 #
www.wfsoftware.co.uk