Is Dynamic String Execution ALWAYS bad?

I have always worked on the principle that DSE is always evil, and should only be used as a very last resort. However, I have been having a really interesting conversation with Kimberley Tripp (find her Blog here) this weekend, and I may have changed my mind...

So first off, lets set the scene... Imagine that you have a multi use stored procedure, that accepts multiple parameters, and different queries pass in widely different values, or even similar values with highly variable selectivity.

The first time you run the stored procedure, or the first time it needs to recompile, (due to stats updates, being kicked out of the cache, etc) SQL will use "parameter sniffing". This is where it builds the plan based on the first set of values you pass in. If, the next time you run the procedure, you give a value with a very different level of selectivity (i.e. The first time, you pass in 'Banana Custard' and the second time, you pass in 'B%') then SQL will use the original execution plan, which will obviously not be the optimal one.

To get around this issue, you can use local variables in the stored procedure. The problem with this, is that the local variables are unknown at compilation, which means that when SQL will use statistics that are based on an overall average of values, rather than from the histogram. This means that once again, you can end up with a sub-optimal execution plan.

So, to get around this, we could use OPTION(RECOMPILE), which means that we force SQL to use a "one-use" plan, and you will get a new plan every time it runs. This issue here, is that OPTION(RECOMPILE) has several issues including (depending on service pack level) returns incorrect query results.
So to get around this, (and yes, we are finally there...!) use dynamic string execution with EXEC(@Query). Although it is possible for this plan to be reused, it is pretty unlikely, and it is essentially treated like an ad-hoc query.

Using this technique comes with it's own challenges, not least the risk of SQL Injection, but you can mitigate these risks with simple techniques, such as ensuring you use the "principle of least privilege", using EXECUTE AS clause in your procedures, using the QUOTENAME() function of parameters you pass into the procedure, etc.

So in conclusion, yes there are times, when dynamic string execution is the best method. You learn something new every day!

A big thank you to Kimberley for helping me get my head around this one!