question

I will always fall on the side of using stored procedures, because they are a much much cleaner interface to the database than queries dotted through source code. For example, if I know that stproc_ValidateUserLogin takes a login name and a password hash, and gives me back a user level - then do I really need to know whether that procedure accesses tblCfgUsers or tblCfgUsersNew underneath?

The other main key for me is in terms of producing reports. The way that I engineer things is to get as much of the job done as close to the source as possible. If I can return 15 rows of report to a client app, instead of 100,000 rows of source data, then that is a good thing. I am also more than willing to admit that SQL Server will do a better job of handling and aggregating that data than I would ever do in my own code...

The optimisation argument is probably tired now, seeing as the query optimiser now does a much better job of making plan re-use apply to ad-hoc queries as well as stored procedures.

Isn't this argument more of the 3-tier variety? application - business logic - database? I have to admit I didn't listen to the podcast.

It all depends on your setup, but I always considered the practice of using stored procedures was a way of giving smaller setups a middle (business) layer without having to create a dedicated middle layer.

I would think there's always going to be shops that want 3-tier setup using only 2 real tiers.

Just as a general comment, this type of question might be better at the main SSC site then here. I doubt there is any clear answer to this question.

With that out of the way, I do see ORM's such as Microsoft's Linq and SQLAlchemy for Python reducing the prominence of stored procedures, especially as they get better at what they do.

With that said, I think stored procedures have advantages that will keep them around for the forseeable future.

1. As mentioned by Grant Fritchey, they can give you an added layer of security. In some situations this is of no concern at all, but in some, especially if people from outside the company need access to write certain types of code, this can be of great significance.

2. At least for the time being, a hand written stored procedure is often more effecient than an ORM. This is especially true for more complex queries. This may change in the future as ORMs and the database engine evolve, but it will remain true for the forseeable future.

3. There are some tasks that are strongly set based that are simply better handled in the set based SQL than they are in Object-oriented/procedural/functional languages (though functional is somewhat closer). You can of course simple compose the SQL directly and have the application send that on to the database engine, but then you lose many of the advantages of SQL development tools like SSMS.

4. Centralization. If a set of logic to be used with SQL is needed by multiple applications it often makes most sense to have it centralized within the DB itself. You can mimic this by putting that code to generate that SQL in a shared library of course, but that becomes awkward if the applications are in multiple languages. By using stored procedures you have one centralized place for that shared logic.

I tend to agree with Grant's analysis (And voted it up). Timothy has a good comment as well.

I think the performance benefits are small at this point. The optimizer is very good, and even the ORM code is getting better. So is there a reason to use them?

For most people I would say yes. Timothy's premise of centralization, or code reuse, is a big one. You can get some of this be encapsulating the calls in a single method in an OOP client, but is that any better? Why not just write the procedure? I think you are splitting hairs here, and in that case, stored procedures are tuneable, and if you had to go cross platform, you could write the efficient procedure needed on each platform, without changing the client.

Security is the big one for me. Better control, and if you avoid dynamic SQL, you can avoid lots of SQL Injection.

Stack Overflow, and specifically Jeff Atwood and Joel's programmers are very, very disciplined, and very smart. I think what works for them doesn't necessarily work for others.

I find another reason to use stored proc's is dependency. If you have to make a change to a table or view, is sure is nice to know what objects depend on the table or view. With sql in the application, it is much more difficult to know what you have to test when making that kind of change.

I am pushing to put a policy in place to make all datasources for reporting service be stored procedure based because of this problem.

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.

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.

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.

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.