Sorry, Brett, the correct answer is "it depends", like Gianluca and others rightly pointed out.

If it weren't for company confidentiality, I would love to post the SP that generates dynamic SQL comprising nested CTEs to solve formulae stored as SQL strings in the database that consume further formulae and/or aggregated raw data and so on, involving differing aggregate operations, AVG()/SUM()/COUNT etc. at each level, accommodating differing periodicity of inputs, requiring pre-aggregation or not, converting signal counts to digital readings or not, drawing from cached results or raw data and more - 'nuff said as to complexity? And then issue a T-SQL Challenge to come up with a solution that completely obviates dynamic SQL... Under SQL 2005 one of our formulas expands to the point where over 256 tables are involved in the query and it gets rejected upon exec... NEVER is a word that is blown out the door by this core process... And the typical dynamic SQL runs in around 0.5 seconds on multiple tens of thousands of rows drawn from a base table comprising 2,104,604,629 rows - GO DYNAMIC SQL!

If you run Kimberley Tripp's revealing cache query at Plan cache and optimizing for adhoc workloads you'll see that IF you run dynamic SQL, you CAN see plan reuse. Even the monster described above achieved plan-reuse.

Be careful that you look at every problem with an open mind...;-)