First of all lets check what both commands mean:
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.
exec: 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, CLR (common language runtime) stored procedure, scalar-valued user-defined function, or extended stored procedure. The EXECUTE statement can be used to send pass-through commands to linked servers.
some of the main deferences:
sp_executesql allows for statements to be parameterized, Therefore It’s more secure than EXEC in terms of SQL injection
sp_executesql can leverage cached query plans, The TSQL string is built only one time, after that every time same query is called with
sp_executesql, SQL Server retrieves the query plan from cache and reuses it.
- Temp tables created in EXEC can not use temp table caching mechanism.
I found the following article regarding the performance:
The performance is a matter of debate between these two methods for stored procedures. As its name suggests,
sp_execute is itself a stored procedure, which stores in the system database.
SP_ExecuteSQL must require passing SQL strings to it thus, excepted to showing higher chances of caching, consequently leading to perform better when run for the second or later on times.
In other words, its parametrized dynamic T-SQL encourages its reuse. Moreover sp_execute is supposed having higher chances for avoiding unnecessary compilation while executing a dynamic query over
But some experts take it as misleading as they think for both methods a plan will be cached. In fact, for the non-parametrized queries of
SP_ExecuteSQL shows the same characteristics as the later one.