One way to do this is to use SQL Passthrough. You can construct the select statement in VFP then send it to SQL Server. It looks something like this:
liHandle = sqlconnect() lcCmd = "select somefields from sometable where someexpression"
liResult = sqlexec(liHandle, lcCmd)
if liResult > 0 && returns -1 if an error occurred . . process results . . endif
>In analyzing a migration our VFP app to SQL Server, I'm trying to learn more about the capabilities of SQL Server 2000, especially in the area of stored procedures. > >Our existing VFP app runs some 'moderately' complex SQL queries, usually by building strings on the fly and then macro-expanding. So by the end, we're usually doing something like... > >SELECT &cSummaryLine ; (these would sum multiple fields) > FROM &cFiles ; (could be as many as 5-6 tables) > WHERE &cJoinConditions ; (often > 200 chars in length) > GROUP BY &cGroupBy > >My question is whether anyone has gone through the effort of transitioning things like this to stored procedures in SQL Server, and any experiences they'd like to share. > >Also, is there a really good book (i.e. many examples) on stored procedures and programming in SQL Server? > >TIA, >Kevin