>>SELECT * FROM TABLE WHERE FIELD IN (1,2,3,4,5,7,8,9). What number is missing? How long does it take you to determine one is missing? That's just single digit ID's. Why should you have to be concerned with that?
>
>If I only have a few keys, why would I use a route to create a temporary SQL Server table and negotiate with that with a IN table clause?
>
>>select * from table where field in (select key from userschoices) is much, much cleaner. It provides far more than 32000 keys. If I understand the intent, the user can select many ids - whether you concatenate them into a string or add them to a table - is little difference to the user. But using a single technique - adding them to a table and not trying to decide if you want to use IN in one case and a temp table in another is also much easier on you.
>
>Yes, the syntax might be cleaner but it requires the setup of a temporary table, which is overkill in such situations.

Michel,

Did you really understand my code example. It does not create a temp table, but the function returns a table from the input parameter that can be used directly in the call

You can use

SELECT * FROM myTable WHERE ID IN (SELECT Item FROM dbo.udf_list2table('1,2,3,4,5',','))

or from VFP

SQLEXEC(nHandle, "SELECT * FROM myTable WHERE ID IN (SELECT Item FROM dbo.udf_list2table(?cMyListofIds,','))","Result")

you can pass the list of IDs, in the cMyListofIds variable.


This works perfectly, for every case where the number of keys can vary hugely. Personally I'd be surprised if this performs worse than what you are doing now.


Walter,