Am I right in assuming that a stored procedure that contained SQL with bound parameters would be faster than using dynamic sql in a string using Execute?
SELECT @field1, @field2 FROM @tbl1
EXECUTE 'Select field1, field2 FROM table1'
If this is true is it possible to have a variable number of parameters like a dynamic fieldlist? Also if there's a good topic in BOL I can look up that might explain these different approaches let me know. THX!
You are correct in your assumption that an SP performs better than dynamic SQL (an ad hoc query). I can think of ways to make an SP return different recordsets based on parameters that are submitted, but I'm not certain that I can think of a way to make an SP return a completely dynamic set of columns based on a submitted field list.
There are many drawbacks to using dynamic SQL, but the performance hit in using dynamic SQL vs bound SQL is not large. The hit comse from the fact that dynamic SQL is not precompiled, but the SQL Server optimizer caches query plans and will reuse them if it finds a similar statement, even if the statement is not identical.
There are occasions when dynamic SQL runs significantly faster than direct SQL that is loaded up with conditional WHERE clauses because the cost of executing the complex query is greater than that cost of recompiling the simpler one.
Be aware that there are other drawbacks to dynamic SQL, and it is best to avoid it if possible. (Right, Brett?)
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.
Never enter a contract with MS...always an escape clause
Note If object names in the statement string are not fully qualified, the execution plan is not reused.