SQL Server–Always use sp_executesql over exec

Today I was looking through some legacy code and found a loop that dynamically created a SQL string and executed using exec().

A quick check of the execution plans showed that SQL Server was creating a new plan for each loop.

We changed it to use sp_executesql instead.  Because this uses parameterised queries, it can re-use the execution plan each time.

Final result – our routine ran 9 times faster and as a bonus it used about a 1/3 less CPU.  Happy days!