>Native data Senario.
>localcur
>select somefields from mytable where mytable.keyfield in (select keyfield from localcur)
>the above works fine with native VFP data.
>
>SQL data senario
>localcur
>*** the localcur can not be used in SQLEXEC, therefore i populate the data into an array:
>select keyfield from localcur into array myarray
>Is there an SQL syntax similar to:
>sqlexec(lhandle,[select somefields form mytable where myrable.keyfield in myarray)
>Please note: I would like to avoid the creation of a string with data from the array.

Hi Yiorgos,
The most effective solution I found is to send the values as a string parameter. Using a code based on Jeff Moden's CSV splitting code, converting that string into a table on SQL server side and joining works wonderfully well. Key points:
-Though you send a string the data you send is less compared to other methods
-Sending the string as a parameter the length is not limited (well VFP's limits come before SQL server's limits but 16Mb documented limit should be sufficient for most operations)
-Converting the string back into a "tally" table on SQL server is very fast with Jeff's approach.

Here are some codes:
* VFP (I use ADO with CursorAdapters so code is primarily for ADO commands but easy to convert to SPT)
* ListToString

Lparameters taArray, tcCommand, toCommand,tnOccurence
EXTERNAL ARRAY taArray
** Create an in ( list )
Local lcCurType, ix, lcValueList, lcSplitterType
lcCurType = UPPER(Type('taArray[1]'))

DO case
CASE m.lcCurType $ 'DT'
	lcSplitterType = 'd'
CASE m.lcCurType $ 'IN'
	lcSplitterType = 'i'
otherwise
	lcSplitterType = 'c'
ENDCASE

If m.lcCurType $ 'DT'
	For ix = 1 To Alen(taArray)
		taArray[m.ix] = Iif(m.lcCurType = 'D',;
			TRANSFORM(Dtoc(taArray[m.ix],1),'@R 9999/99/99'),;
			TRANSFORM(Ttoc(taArray[m.ix],1),'@R 9999/99/99 99:99:99'))
	Endfor
Endif
lcValueList = ''
If !(Lower(m.lcValueList) == '.null.')
	For ix=1 To Alen(taArray)
		lcValueList = m.lcValueList + ;
			IIF(m.ix>1,',','') + ;
			TEXTMERGE('<<taArray[m.ix]>>')
	Endfor
Endif
toCommand.Parameters.Append( toCommand.CreateParameter("@"+SYS(2015),201,1,-1, m.lcValueList) )

RETURN TEXTMERGE(;
  Strtran(m.tcCommand,'(?)',;
  '(select [value] from <<m.lcSplitterType>>Splitter(?))',EVL(m.tnOccurence,1),1))

SQL Server i,d and cSplitter functions:

Create function [dbo].[iSplitter] (@Parameter VARCHAR(MAX))
RETURNS @splitResult table (number int, [value] int)
as  
begin
SET @Parameter = ','+@Parameter +',';

WITH cteTally AS
	(
		SELECT TOP (LEN(@Parameter))        
			ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N   
			FROM Master.sys.All_Columns t1  
			CROSS JOIN Master.sys.All_Columns t2
	) 
insert @splitResult
	SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
	SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS [Value]
	FROM cteTally  
		WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ',' 
return
end

dSplitter and cSplitter- difference is in return table definition:

-- ...
RETURNS @splitResult table (number int, [value] datetime)
-- ...

-- ...
RETURNS @splitResult table (number int, [value] varchar(100))
-- ...

I create a command like:

"select * from myTable where myIdField in (?)"

I have a procedure(s) that take the raw SQL command along with parameters. It parses the command and check parameters, seeing array parameter exist, sends to ListToString something like:

lcCommand = ListToString( arrIdList, m.lcCommand, loCommandObject, 1) && there maybe multiple arrays passed - 1st array

Cetin