Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
sp_executesql
is more likely to promote query plan reuse. When using sp_executesql
, parameters are explicitly identified in the calling signature. This excellent article descibes this process.
The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".
The big thing about SP_EXECUTESQL is that it allows you to create parameterized queries which is very good if you care about SQL injection.
Microsoft's Using sp_executesql article recommends using sp_executesql
instead of execute
statement.
Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.
So, the take away: Do not use execute
statement. Use sp_executesql
.