I'm a big fan for a couple of reasons...

1. A lot of front-end developers don't know how to write proper, safe, and performant complex T-SQL especially when it things like "catch-all" queries. I'd rather see that type of code in the back-end rather than as embedded code.

Here's the best article ever on "catch-all" queries that are actually SQL Injection Proof.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

2. It really helps as a DBA for creating things like index maintenance, etc, etc.

A lot of people have a bit of a problem writing and debugging dynamic SQL. I normally just write code that works well for a single iteration of whatever I'm trying to do and then parameterize it. I normally use the methods that Gail shows in her "Catch-All" query article but I do substitute double quotes for single quotes and simply replace them at run time. Makes for some really clean and easy to read/write dynamic code.

I say "normally" because I do have to occasionally write some non-public-facing SQL that traverses tables and databases. Heh... and, no... I don't use sp_MSForEachTable or sp_MSForEachDB.