Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.
Ever heard of Access?
Pretty nice thing.
Comfortable design tools, front end and reporting - all in one package.
And the best part: it will beat any ORM/Linq based solutions in terms of performance and scalability hands down.
ORM/Linq solutions only good until the whole database fits in memory.
Linq uses deferred execution so never holds more than it needs to in memory. As I said, and we agree, one should use SPs for the heavy lifting. The issues that Hugo was talking about (joining to other tables, filtering, aggregation, preventing sql injection & server round trips per row, storing logic etc) are all now handled very easily in the ORM/application layers.
Not buying your final conclusion under any circumstances. ORMs are not designed for specifics, but instead are designed for generics, and rarely are they well-designed, even for that purpose. In most cases, some critical functionality has to use a query building engine that is not very good and the queries created are crap from a performance perspective. It's consistent, and repeated. And clearly, they don't all solve all of those problems. Very few solve any of them. And the more they stay away from stored procs, the worse they are at solving them.
ORM's are designed for specifics too. However, at the end of the day, you just define a class model in your application. Either you as the database professional is going to define it or the ORM is going to define it. IN both cases, it's likely going to be the exact same approach minus data size limits and maybe data types, but even then, you can give the programmer recommendations and process on what to follow for best practices there when he defines the class.
The major issue is the methods of the classes and how people read and write data from those models. This is where things can get hairy, but for the most part, they are mostly pretty straight forward approaches with interacting with those data models you would create anyways. It's not rocket science for the ORM. The only major issue there is how poorly those models are designed along with the methods, but that's no different than poorly designed models and SQL from database professionals that happen all of the time too. It's not SQL or ORM that's the problem, it's the people and their ability to use these tools.
And until you wipe out all the insufficiently database-qualified developers, ORM's will continue to cause more trouble than SQL Server alone will. Not having the ORM around tends to force you to hire better talent - at least if you want to not destroy your business through IT failure, anyway...