Question

What is the difference between EXEC vs sp_executesql?

Answer 1

Besides the usage, there are some important differences:

1. sp_executesql allows for statements to be parameterized. Therefore It’s more secure than EXEC in terms of SQL injection
2. 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
3. Temp tables created in EXEC can not use temp table caching mechanism