Menu
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 ?
973
The main difference between the EXEC or EXECUTE operators and the sp_executesql built-in stored procedure is that the EXEC operator is used to execute a stored procedure or a SQL command passed as a string or stored within a variable.
sp_executesql allows for statements to be parameterized, Therefore It's more secure than EXEC in terms of SQL injection.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
Static or Embedded SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries.
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".
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
