The title says it all... What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server with
EXEC(@SQL)
versus
EXEC SP_EXECUTESQL(@SQL)
?
views:
3733answers:
2
+2
A:
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.
DJ
2009-02-13 23:28:17
That is not the main benefit of sp_executesql. You can paramaterise a query without it
Mitch Wheat
2009-02-13 23:33:25
I don't think you can parameteize a dynamic sql without it??
DJ
2009-02-15 00:34:59
EXEC('SELECT * FROM FOO WHERE ID=?', 123) will replace the parameter placeholder "?" with the value 123 and then execute the query, returning a result for SELECT * FROM FOO WHERE ID=123
Peter Wone
2010-06-01 23:27:39
Oops, that syntax is only available for linked servers.
Peter Wone
2010-06-02 00:26:15
+8
A:
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 "The Curse and Blessings of Dynamic SQL".
Mitch Wheat
2009-02-13 23:29:49