SQL Server is a *very* powerful platform. At first blush, one might not think that SQL is capable of some of the more complex data manipulation capabililities that exist in VFP. However, as you delve deeper in SQL, you will find that row by row scanning operations on cursors are possible. i.e., you are not limited to just purely SQL commands.

Stored Procedures not only return result sets, but they can return individual values as well. If you like, you can return both rows, multiple row sets if you wish, as well as output variables.

As far as a books are concerned, here is my recommended list of resources:

SQL Server Magazine
Inside SQL Server 7 (Kalen Delany) (Have not heard about an update for 2000)
SQL Server Books Online (Actually, they are quite good)

In addition to these resources, if you are going to use SQL in conjunction with VFP, you are going to want to have a solid methodology in your toolbox. For this, I would suggest checking out (www.redmatrix.com)

As for your question, you can use strings in SQL Server stored procs via the Execute statement. However, from an optimization standpoint, I don't think SQL Server creates a plan for these items. Therefore, I don't think they can ever be fully optimized. However, that might not matter depending on the size fo the data set you are dealing with.

Alternatively, you could create the SQL on the client and render it to sql. While I am not a fan of client-side rendered SQL, I understand there are times when you need to do it. I like to keep Select privledges for tables to an absolute minimum number of users - with 0 being the optimal.

In order to prevent people from sending queries to the server that could potentially bring down your server, it requires a robust interface to check for problems and or prevent problems before passing string to the server.


Good Luck!!







>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