>>>Fitting this into the available categories was guesswork...
>>>
>>>Suppose that I had a successful product deployed in many businesses and that it used exclusively native VFP tables.
>>>
>>>Suppose too that many of those customers had long ago migrated their other applications to SQL Server or Oracle or whatever and that the product in question was the one remaining application NOT using SQL Server (or ???). By now several of these installations find the subject application to be a "PITA" if only because they no longer have expertise or because it spoils their "all SQL Server" status.
>>>
>>>So I decide that my wisest course is to integrate "client server" into the application.
>>>
>>>Now most of this application already uses (VFP) SQL - specifically Select and INSERT INTO. Many parts of this application uses, for lack of a better term, 'sequential SQL - Select' commands to obtain a result set. In other words there may be 3 - 8 Select - SQL statements in a row, one taking the result set(s) from prior commands and using them to eventually get at what's needed.
>>>
>>>My understanding is that, using strictly 'passed-through' SELECT commands will NOT work in such cases because the result set(s) sit only on the local PC and so are not available to the (data) server for subsequent use.
>>>But I also understand that these could all be used more or less as-is IF THEY WERE EXECUTED FROM A STORED PROCEDURE.
>>>
>>>Assuming this to be correct, the question at hand is: Is it reasonable (maybe even 'expected'?) to require the installation of stored procedures into a customer's existing system?
>>>My sense tells me it IS reasonable, if only because these stored procedures can be isolated to the specific database(s)/tables relevant to my application. But I don't know how such would be viewed/accepted given the myriad of factors about which I know nothing.
>>>
>>>Your counsel is appreciated.
>>
>>Jim,
>>IMHO highly reasonable. Stored procedures would be part of your database. Not letting it is kind of not letting your database and tables into SQL server.
>>Also you could create those procedures as temporary procedures and run (prefixing the procedure name with ## -temp to all connected- or # -temp to local-). (Or use sp_executesql).
>>Another consideration might be to think if you need it all. ie: If all of your subsequent SQLs are derived from the first then you might simply SQLExec() the first and SQL natively rest locally.
>
>Thanks for that Cetin.
>Regarding your last point, do you mean to send all (3-8) SQLs in a single SQLExec? I understand that something like that can be done and that results sets for each are returned. IF that's close to correct < s >, and I really only need the last one, is that possible?
>
>cheers
>
>>Cetin

Jim,
Which one of last points :) If you mean sp_executesql, it's almost like calling a stored procedure (performance might be less but sql server might use the same execution plan used on previous calls). ie :

local lnHandle
lnHandle=SQLStringConnect(;
  'DRIVER=SQL Server;SERVER=servername;Trusted_connection=Yes')

m.ldDate = {^1997/08/21}

Text to lcStatement noshow
'
select * into #c1 from Northwind.dbo.orders
   where ShippedDate = @ldDate
Select * from Northwind.dbo.employees emp
  inner join #c1 my on my.employeeID = emp.employeeID
'
endtext  
  
lcParams = "'@ldDate datetime'"
lcParamValues = "@ldDate = ?ldDate"
if lnHandle>0
	SQLEXEC(lnHandle, "Exec sp_executesql"+;
	" @stmt = N"+lcStatement+;
	", @params = N"+lcParams+;
	", "+lcParamValues , 'myResult')
	SQLDisconnect(lnHandle)
endif

If you put aside the unnecessary 2 SQLs here done on purpose for sampling :
-An SQL selects some records (parametric) from orders into a temporary table called c1
-Then another SQL joins this c1 with employees to return the employee details

2 SQLs are done on server and only one result is sent back.

This approach while works is arguable, but works:)

If you meant "If all of your subsequent SQLs are derived from the first then you might simply SQLExec() the first and SQL natively rest locally." :

If your process needs only one more more resultsets to be retrieved from SQL server that are not directly related but would somehow use them on complex computations etc., you might first get the resultsets, then do the complex parts into native VFP cursors at VFP side. Hard to find a sample for this right away, but at least I know in my apps there are some that perfectly fits into this. Doing them on the server all and returning only just the result is not efficient (especially if all I need small subsets from multiple tables - the total of those result sets are much less than the set I need at the end of computations).

Cetin