Subject Re: [firebird-support] How do you dynamically build a SQL Statement in a Stored Procedure?
Author Helen Borrie
At 03:13 AM 2/05/2006, you wrote:
>I am trying to build a resultset from a list of ID Values. I would
>like to do this in a stored procedure rather than in Delphi code
>because there are lots of queries to build the resultset, so it should
>be more efficient to do it in the database.
>1) How do I pass a stored procedure a list of ID values?

Pass a string as a varchar of sufficient length and write a procedure
that's called within your procedure to parse out the individual
values successively in a loop.

>2) How cam I dynamically build a statement like Select X from Table
>where ID value in ( Value list )?

In DSQL you can't (yet) of course pass a list as a replaceable
parameter (although there has recently been Big Architectural Talk
about implementing something along these lines in Fb 2 +. If you
decide to write a SP, you would invoke the SP as
select blah, blah1, etc... from MySP(:list_as_a_varchar)
- meaning you still need to construct the list in your client code.

So - it is "doable". It's moot whether you would actually gain a
speed advantage but it might meet your expectations in terms of simplification.