--- In firebird-support@yahoogroups.com, "Larry Hengen" <lhengen@...>
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?
> 2) How cam I dynamically build a statement like Select X from Table
> where ID value in ( Value list )?
>

For small datasets, this will work, but be aware of the limits of the
SQL parser. In particular

Maximum Query size: 64K
Maximum members in an IN condition: 1499

Also understand that there is an index hit for every member of the in
statement, so

select *
from tablea
where ID in (1,2,3,4,5,6,7,8,9,10,12,13,14,15,20)

would hit the index 15 times, yuck.

If you still want to pursue that path, then you would have to
investigate the EXECUTE STATEMENT syntax in the Firebird 1.5 release
notes. It is a bit messy and will need to prepare the query for each
execution.

Our solution to this problem was to parse through the dataset and
generate our own optimised condition clause that first attempted to
form between statements, and then placed any values that could not be
put in that form into an IN statement. Actually if there are over 1499
values to go in the IN statement, it also splits it into chunks of 1499.

So the above example would be converted to:

((ID>=1 and ID<=10) or (ID>=12 and ID<=15) or ID IN (20))

This statement only hits the index 3 times.

The other way you could go is to insert the selected values into a
table, then join to it. The insert does take time though, and this
must be factored in as to whether it is acceptable. You would also
need to cleanup the records that are inserted.

Adam