>>>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?
>>
>>For SQL 2008 and up I suggest to use table valued parameters and pass all values this way. Otherwise pass as a comma-delimited or XML and use a function to split (or split inline). Don't use IN (?list)
>
>Can you give me an idea of how I would use table valued parameters from VFP, please?
>
>Assume I have a cursor with all the PKs that I want and I am building up my SQL like this:
>
>

LOCAL lcSQL
>TEXT TO m.lcSQL NOSHOW TEXTMERGE
>	SELECT claims.*
>	FROM Claims 
>	WHERE clm_pk IN (?m.lcListOfPKs)
>ENDTEXT

>
>and then I do an SQLEXEC with that.

text to lcSQL noshow
   declare @t table (Pk int)

endtext

select myCursor
scan
   text to lcSQL additive textmerge noshow
     insert into @T (values <<myCursor.Pk>>)
   
   endtext
endscan

TEXT TO m.lcSQL NOSHOW TEXTMERGE additive
	SELECT claims.*
	FROM Claims 
	WHERE clm_pk IN (select Pk from @t)
ENDTEXT