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.