IMO if you have a "that should never" attitude towards dynamic sql or most other design decisions, you are doing your clients a disservice. Technology should be used to solve problems and often stored procedures creates another layer of debugging and specialty knowledge inside a company rather than solving a core problem. If you need your SQL Server to run at peak performance, you can't ignore the advantages of stored procedures however don't write off dynamic sql it does have advantages and they aren't minor in some instances.
In our company, our middle tier generates obscene amounts of SQL for us. Given the size of our application and limited resources, spending that time coding and maintaining stored procedures is man power better spent elsewhere. Do our SQL Servers run at optimum speed, no. However, that wasn't a criteria for the project and hasn't been a problem.
Our design doesn't fit all scenarios, but more often than not before DBAs even hear how the project is supposed to work they just jump on the Stored Proc bandwagon and think a company is retarded for not using them. They both have their place.
Beauty is a tenuous term at best. Solving the problem and providing a solution are in a sense - beauty. Take a datagrid and plug in sortable columns custom user-selected page sizes and have it display a fair amout of data in 10 or more columns and you are "bad" because you have "dynamic SQL" and, heaven forbid, you can read it in the code-behind. I would suggest that for most, this solution is "beauty".
As a rule, the simplistic answer is that to defeat SQL injection, we must use stored procedures. Okay, so when that is done, what is the next crisis that will be created by the ne'er do well hackers of the world? I support Stephen's theory and thank him for some insight.
As with most things in life, dynamic SQL is great within context. Are GOTO's bad? Is VB bad? Is religion bad? Are guns bad? There are those who would exclaim "Yes" to each of those statements, but within context each of these are very useful and even elegant.
A little philosophical, maybe, but I'm just sayin' I agree with you. Use the best tool at your disposal when you need it.
So, to sum up the article... it depends.
That should be answer that DBA's use in most situations.
The thing is, there really are solutions (your example being one) where dynamic SQL is not only acceptable, it's preferred. Unfortunately, usually, when you see this religious debate going on, it's not between reasonable people. It's between code zealots, who don't/can't/won't deal in set based logic, treating TSQL as just another part of the coding architecture to let it do what it does best in ways that improve performance and elminate code reuse, versus DBA zealots, who don't/can't/won't deal in speed and flexibility over control and stability, treating all applications as interlopers into the sanctum sanctorum of the clean-room database environment who'd better wipe their muddy-assed boots at the stored procedure door. These two camps don't want to change.
The zealots aside, most of the time when I read about (or deal with) developers that are insistent that they MUST have dynamic SQL, it's because of a lack of knowledge. They can't understand set-based logic so they try to treat databases like flat files, writing out one line/row at a time. They don't have a good grasp of their own data access mechanisms, for example, they don't know how to pass parameters to stored procedures through ADO.NET. In these cases, while it's a pain the ass, taking the time to walk them through why stored procs are good things, how to use them, how to call them, reaps long term benefits.
Of course, I can just take out the hickory stick & go all Buford Pusser on their heads too. While that doesn't always help the developers, I feel better afterwards.
Nice article. I'm sure it's going to wake the zealots up again.