Actually that's what I had (I made a mistake in my original post).
It turns out that the query was not the problem, it was actually bringing out all the requested records but I was then exporting the result to Excel using SweetPotato Software's Excel stuff and that was choking on a bad date that had been entered by the user (21/02/0011) so I need to put some date validation in my system.
Using the
IN (<< m.lcListOfPKs >>)
is actually faster than using the table valued stuff, my only concern is if there is some limit that it might have.
>Try
>
WHERE clm_pk IN (<< m.lcListOfPKs >>)
>
>>Hi,
>>
>>I've been using a SQL query that uses a list of primary keys as a parameter to bring out all those records that a user selected.
>>
>>The logic I use is:
>>
>>User selects a bunch of records in a grid/cursor, I build up a list of all the primary keys (PKs) selected and pass this list to a method that builds up an SQL command using:
>>
>>WHERE clm_pk IN (?m.lcListOfPKs)
>>
>>The user has reported that when they select a large number (2000 odd) the result does not include all selected and seems to stop at a particular count (1800 odd). Is there a limit of how many items SQL server will use in an IN clause? Or maybe this is a VFP limit in terms of how long the string that I build up with the list of PKs can be?
>>
>>Is there any other/better way to pass a list of Primary Keys from VFP to SQL Server in order to limit a result set?