The never ending stored procedure debate

I listened to the panel discussion on the pros and cons of stored procedures from the currently ongoing TechEd09 today. It was not what I hoped for, the panel consisted almost exclusively of pro stored procedure people with the exception of Jeffrey Palermo who for an NHibernate guy appeared very pro stored procedure.

I was hoping for a more balanced debate. The arguments were to much focused on the real vs. perceived benefits of stored procedures in terms of performance, database coupling, vendor coupling, security etc.

The really big issues I have personally with stored procedures (sprocs from now on) were never fully brought up. Especially when you compare sprocs and a manually coded DAL (which I find is the most common) with NHibernate.

Code duplication
In my experience systems which rely heavily on sprocs also show a large amount of code duplication, for example duplication of SQL queries in the same sprocs in the case of dynamic queries that filter on different columns based on input. I have seen sprocs that feature the same basic query duplicated 12 times with small variation in the where/order clause. Also duplication between different sprocs can usually be very high. And the final point is the sad fact that sprocs usually contain some business logic, logic that sometimes also exist in the application itself.

Productivity & Lines of code
This topic was also not really touched upon. Data access layers which use sprocs often feature massively more amount of code to deal with calling the sprocs and mapping them to entities. The amount of TSQL you need to write for the basic CRUD sprocs is also a huge time waster and possible maintenance nightmare.

Some of these issues could be argued that it is just incompetent programmers/DBAs and sprocs are not to blame. Maybe it is not fair to compare sprocs with an ORM like NHibernate. But I think you can compare having to write and maintain sprocs compared to letting NHibernate generate adhoc SQL. Sure I think sprocs still have their usage in specific and relatively rare scenarios but the panel discussion too often concluded on the wishy washy "it depends". Of course it depends, context is everything (as Scott Bellware always says), but that does not mean that one method shouldn't be the preferred "best practice" choice.

Sorry for the rant. Kind of frustrated with a current legacy system (which uses sprocs) :)