Subject alternative to an IN clause...
Author Greg At ACD
Hi all,


I have a Firebird 1.5.2 database that I am accessing using Visual
C++ (.NET 2003) and using SQLAPI for DB access

Our application needs to make a call similar to the following:

SELECT * from myTable
WHERE ID IN (1,2,3...,n)

and the number of values that will be in the IN statement will vary
anywhere from 1 to 1000 or more (theoretically).

From my client application, one option is to simply generate the
statement above (in reality the statement is much more complicated
than this, but this post specifically deals with the IN clause) by
formatting a string containing a comma separated list of values
(they are currently in a simple long array of numbers).

I'm wondering if there is a more efficient (or a 'best practices')
way of doing this? Perhaps dumping the values in a temporary table
and doing an inner join to this? I would think that the overhead of
doing 1000 insert statements before executing the query may not be
very efficient. Perhaps using a selectable stored procedure that
takes the comma-separated list of numbers as input, and do a join to
this virtual table?

Any help here is certainly appreciated... i can't imagine that this
is a new problem.