Question: Could you explain the difference between sql sp_executesql and EXECUTE (EXEC) ?
Answer: There are a number of similarities and differences of SQL sp_executesql and EXEC. Let’s start by looking at the SQL Server BOL definitions:
Sp_executesql = “Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters”
EXECUTE = “Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.
Notes on sp_executesql
1) The ad-hoc T-SQL statements are paramaterized.
2) The SQL Server Optimizer reuses the execution plan generated as the T-SQL appears static to the SQL Server Query Optimizer , while only the parameters are changing. This is an advantage ,helping to decrease recompilation cost and improve execution plan reuse. The disadvantage is if a “bad” plan is created , it continues to be reused at every execution
3) SQL Injection Protection - Strong typed parameters assist in defending against SQL Injection attacks.
Notes on EXECUTE
1) Executes any statement construct. Mainly used for stored procedures , but also other character strings
2) EXECUTE does not have strong typed parameters
3) Execution Plans generated by EXECUTE aren’t always cached.
Switching from sql sp_executesql to EXECUTE is not a guarantee of improved sql performance. I’ve seen plenty of situations where the first execution created a “bad” plan. Testing and monitoring is important to optimize response times . I find wrapping up sp_executesql in a stored procedure is an easier method to manage the variable options, as well as offering futher benefits to be maintained as part of a consistent code base
Read More
SQL Server - Stored Procedures versus ad-hoc paramaterized queries.Which is faster?
SQL Server - Stored Procedure Checklist - SQL Server DBA
SQL Server - How to improve Execution Plan Reuse - SQL Server DBA