Why sp_executesql is better than EXEC for writing dynamic queries?

 Posted by Rajnilari2015 on 3/16/2016 | Category: Sql Server Interview questions | Views: 413 | Points: 40
Answer:

- sp_executesql allows for statements to be parameterized. Moreover, the parameters are strongly typed. So it mitigates the risk of SQL injection which is very common in case of EXEC since the query prepared by this is an adhoc one.

- For dynamic SQL too, the plan gets re-used while using sp_executesql. The execution plan that gets generated on the first execution of the SQL query is re-used in the subsequent executions. Henceforth, it is faster as compared to EXEC method. In case of EXEC, since adhoc statements are generated on every query execution, SQL Server needs to recompile for every subsequent execution.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response