Stored procedures vs a business logic layer in code

It breaks down this way, you can build a well-designed front-end application that uses well built parameterized queries and get just about every bit of performance and plan reuse that you can get out of stored procedures. There are two issues that will probably keep stored procedures alive into the future. The first is security. You can do a lot more granular manipulation of security through stored procedures than you can through parameterized queries. That's not needed for lots of applications, but it is for others. The other issue is deployment. I know that some developers and some shops have been able to get deployment down to a fine art so that they can make lots of small code changes and get them into production quickly and easily, so if query tuning is needed in these shops, changes to the code that generates the queries can be quickly rolled out into production. But lots of shops are not able to do this. So having an added layer, through stored procedures, allows for quick deployment and modifications that don't require complete code builds and all the associated testing required.

All this assumes that you're not talking about straight ad hoc SQL. That's still a frightening abomination, performance bottleneck, security hole, and all around problem. I know that lots of developers are in the "deliver the code and leave" mode of consultants. But for those that work full time and have to maintain applications over an extended period of time (I'm working on apps that are over 10 years old and there are 20+ year old apps in the company I work for) making poor design choices, such as ad hoc SQL, have enormous long term repercussions and ultimately have to be replaced with better applications.

In my experience many large organisation keep the database in service far longer than the application that surround them. I have come across instances where the database has 1980 style thick clients, ASP web clients, Excel clients and others. IT departments are keen to build a brand new .NET front end with an ORM layer abstracting away from the database. The problem is that there is a massive impedance mismatch between the object model and the database, which would result in a very inefficient ORM layer. Database re-design is out of the question because it would require re-write of the many systems dependent on the current structure. In this case stored procedures have proved to be the best abstraction allowing a clean interface to the ORM. Also as legacy applications are retired the database can be transitioned to a more ORM friendly design by changing the interals of the procedure with no impact on the ORM.

The counter arguement for the DBA is: I will be responsible for writing, testing, debugging, performance enhancing, documenting, coordinate with the daily build and securing the database and the objects I make available to the developers in a timely fashion.

How could they say no?

There are movements in the agile world that want to get away from overly-specified skill-sets. No one is solely responsible for an aspect of the project. Everyone works on everything and even shares their lunch while singing Kumbaya. To be honest, I like the variety as well.

Here is how I see it. The case with these new Domain Driven Development process is the ability to plug and play data persistence. So in order for us to easily move from SQL storage to a Mock test storage we need business logic out of the database and this would help in testing and promote loose-coupling. When the business logic is on the application layer all we need the database to do is persist the data and be done with it. This way as mentioned above ValidateUserLogin on the business layer would validate the user and not be bothered about where the data comes from, be it from SQL or Oracle or Mock. This is the future, but Stored procedure would not go away that easily unless this method is accepted to the fullest extent.

I look at it this way: A staff with one programmer and one dba would be able to split development a little more equally if the programmer doesn't do all of the: user interface, business logic, and data connectivity. The DBA could code, but why not utilize their expertise as a dba and place some/all of the business logic in procs.

Also, some databases are back ends to apps created by third parties and you can't get to the code. We have an application that allows the creation of custom procs that can be used by datapanels and/or reports inside the application. Makes my job a lot easier and the app is much more flexibile without requiring customizations from the software provider. Of course, you can create your own app that uses the same database.

If you need for some heavy procedural code, avoid a proc. Complicated/lengthy SQL, proc.

As a lone developer, these types of QA are extremely helpful by giving insight into alternative methods.