Besides the usage, there are some important differences:
sp_executesql
allows for statements to be parameterized. Therefore It’s more secure thanEXEC
in terms of SQL injectionsp_executesql
can leverage cached query plans. The TSQL string is built only one time, after that every time same query is called withsp_executesql
, SQL Server retrieves the query plan from cache and reuses it- Temp tables created in
EXEC
can not use temp table caching mechanism