For my regular readers , I will definitely share the good links with the abstract from the same .
To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution” (DSE)
As mentioned in the BOL :
To execute a string, we recommend that you use the sp_executesql stored procedure instead of the 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 – Allows for statements to be parameterized , It’s more secure than EXEC in terms of SQL injection and also like Stored procedure , sp_executesql reuses cached query plans on successive execution , it will be a performance problem in scenario’s where parameter value changes
on successive execution and results in big variation in the selectivity of rows to be returned with same cached plan .
- sp_executesql reduce the risk of SQL injection than EXEC
- cached plan problem with sp_executesql
- Again , EXEC and sp_executesql – how are they different?
- OPTION (RECOMPILE) for cached plan problem with sp_executesql
- How to avoid SQL Injection when using EXEC
- Complete picture – Dynamic Search Conditions in T‑SQL Version for SQL 2008 (SP1 CU5 and later)
And some more …
Related useful topics :
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Execution Plan Caching and Reuse
Parameter Sniffing Problem and Possible Workarounds
Statement execution and why you should use stored procedures
T-SQL Script to check the syntax of dynamic SQL before execution
How to build and execute dynamic SQL queries?
Dynamic SQL & Stored Procedure Usage in T-SQL
As highlighted in the title of this post , security and performance are the key factors to be considered when using dynamic SQL .
****^#( Hope this post was useful !!@:)$(!!