Marios Philippopoulos (2/26/2008)
As Jack mentioned earlier, do use sp_executesql in your dynamic SQL for efficient reuse of execution plans.Even with the 100 possible columns involved here, I'm sure a handful of favorites will take the bulk of choices for sorting, and you will want these execution plans to be re-used as much as possible.
I'd have to say, it depends... I've seen it where reuse of the execution plan gives some horrible performance because of the change in selection method caused by the parameter change. I've recently run into that very problem where the recompile produces the correct answer using a merge join on a million rows in milliseconds as opposed to a half hour long run using a looped join.
sp_ExecuteSQL isn't the panacea that some think it is... it's sometimes better to have a recompile occur.
--Jeff Moden
Change is inevitable... Change for the better is not.