tony@kenny.net (1/3/2013)
I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.
Interested to hear opinions.
Tony
IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other. When future calls generate and execute the same dynamic T-SQL query, SQL Server will use the cached execution plan. So you could end up with separate plans for all possible variants of the dynamic T-SQL query, but as long as the execution plans remain in the plan cache, SQL Server will reuse them each time the same variants are executed.
Jason Wolfkill