What is the difference between EXEC vs sp_executesql?

Technology CommunityCategory: T-SQLWhat is the difference between EXEC vs sp_executesql?
VietMX Staff asked 3 years ago

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