>I was talking with a friend that works with SQL Server 2K.
>I told him that I was using VFP as frontend and using SPT to call Stored Procedures(SP)
>passing the necessary parameters to the SP in SQL Server.
>My intention was to increase speed in SQL Server, than I understand that
>if call more than one time the same SP in SQL Server, it would take advantage
>that it will keep in memory the execution plan, saving the overhead of
>recompiling the statement.

I don't understand, you say you are calling SP's but you want to increase the performance by calling SP's?

>He told me that the way VFP passes the statement using SQLEXEC( ),
>in SQL Server it works like this:
>
>Execute sp_ExecuteSql ‘SELECT au_Name FROM Authors’.

sp_ExecuteSql is used by for parameterized remote views. If you use SQLExec() the sql is passed EXACTALLY as you send it. (At least this is what I have observed when doing tests and watching profiler.

>He mentioned that the Execution Plan is the sp_ExecuteSql and not the SELECT, so if I have a same/silimar SELECT statement I WONT get the benefit of the execution plan in SQL Server memory.

Umm... not exactally correct, here is some info from the SQL Books on line which actually recommend that you use sp_ExecuteSQL for dynamic queries:

********
sp_executesql

Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.

Being able to substitute parameters in sp_executesql offers these advantages to using the EXECUTE statement to execute a string:

o Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

o The Transact-SQL string is built only once.

o The integer parameter is specified in its native format. Casting to Unicode is not required.

************

BOb