> > I Hope this is an easy question, I am using SQLEXEC to run a query on a MSSQL database. I am using a sub query of a cursor I create in the step before this query. Does SQLEXEC support this type of select statement?
> >
> > ~Dan
>
>
> Here's an alternative to Anders' solution.
>
> If you go the way Anders showed you, and there were 25 records in your local cursor, then 25 SQLEXEC commands will be executed... the MSSQL Database will be queried 25 times.
>
> And if you only have 5 distinct customer id's in those 25 records, then you'll end up getting lots of duplicate records.
>
> You can do it with one query like so:
>
>
select distinct CustID from LocalCursor into array laCusts
> lcCustIDList=""
> for xx=1 to alen(laCusts,1)
> lcCustIDList=lcCustIDList+iif(empty(lcCustIDList),"",",")+transform(laCusts[xx,1])
> endfor
> lcSql="SELECT * FROM SS_Table WHERE CustID IN ("+lcCustIDList+")"
> sqlexec(lnHandle,lcSql,"MyResult")
>
> I don't know what the limitation is to the number of items you can list in an IN list. That's the potential drawback with this solution.
>
> --Brad
No limitation on the number of items (at least it is above VFP's capacity) but longer the list is, it gets slower and slower finally MSSql times out.
Recently I needed it badly and I was disappointed to see MSSql doesn't optimize such an in query. Alternatives like creating a temp table and inserting IDs worked better in the sense of capacity but again suffer from a time out (too many inserts whatever you do even with SQL2008's row constructor).
The best solution I found was is to send a comma delimited list and convert that to a table via a 'tally' table on server (based on the idea of SQL guru Jeff Moden) then do a join. It really works fast (ie: 25K+ IDs sent to server responded in less than 2 seconds where it was invariably timing out with a plain IN list).
Cetin Basoz