What is the difference between executing a dynamically built SQL statement @SQL (NVARCHAR(4000) with “EXEC” statement as opposed to the “sp_executesql” statement

- sp_executesql gives the possibility to use parameterised statements, EXECUTE does not.
- Parameterised statements gives no risk to SQL injection and also gives advantage of cached query plan.
- The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters used.
- When you use thesp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.
- sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.