For truly dynamic behavior, do you think moving that responsibility to coding might reduce verbosity, error prone string concatenation, and allow more flexible dynamic searches?
I'm talking about solution s like dynamic linq in c#.
Or using predicates with linq.
That type of solution would be testable using formal unit tests
Well I guess I'd have to say "it depends" (to quote one of my SQL Heroes). I think what I did is pretty darn flexible already. Recall what I said at the end, about this being a small (about 20%) subset of the actual functionality delivered by the Production SP.
The thing I don't like about queries embedded in front end apps is that the code is quite obfuscated. Not only (when in an SP) can you pretty clearly read the underlying SQL even though it is embedded in strings, it opens the code up to scrutiny by those that may not have C# skills (like DBAs). The more scrutiny a code undergoes, the likely better it will be. While you could still uncover the underlying code using Profiler, that's an unnecessary hassle as far as I'm concerned.
Furthermore, I think it depends on the confidence you have in your C# development teams. If they're really good and you have plenty of resources available to support an application through multiple platform releases, you may be OK. SQL doesn't change much from version to version. With the exception of assignment on the DECLARE statements, the SQL I've used here is pretty compliant back through SQL 2000 I'd think (although that statement is untested).
Finally, when your testing team uncovers an error, in C# it may require recompiling and an application redeployment, which in production may mean some app downtime. A fix to an SP can be deployed without that, and I'd venture to say finding the bug and fixing it might take a fraction of the time (if in an SP). This also allows retesting the SP using just the @debug facility.
Just a few things to think about.
Edit: An additional point that occurred to me is that if you're talking about web apps here (and I presume you are), constructing very large SQL query strings and then sending them through for execution on the server side increases the amount of data transferred between the client-side and the server. In most cases I'd think that the call to execute the SP would be a much shorter string. Possibly a minor performance improvement.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]