Why sp_executesql is better than EXEC for writing dynamic queries?

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.