What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using
EXEC (@SQL)
versus
EXEC SP_EXECUTESQL @SQL
?
Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
Viewed 214637
5 Answers
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.
Related