So in today's day and age stored procedures are not a new thing at all. DotNet Developers seem to put themselves in one of two camps, those that think they are awesome and use them for everything, and those that think they only have certain times when they are needed and only use them when the absolute need arises (these days, not regularly).

I am not naive in my understanding that this post is a lot like religious debate, but i am more interested in making people stop and take a look outside the box before a project is started (it’s certainly not the first post of it’s kind) than keeping a low profile.

Stored procedures have grown in functionality over the years with the advancement of T-SQL and can do a hell of a lot more than simply get or update data, but this begs the question of those web developers using them on a daily basis: Are they being used properly or for the right reasons?

To get an answer to this question we have to take a good look at why most .Net web developers (and application developers at large) who use stored procedures continue to, and don't shift to a faster-to-develop, easier to debug method like a managed query tool/active record like the use of Linq-to-SQL (or Entity framework), nHibernate or Subsonic.

I must give full disclosure that my renewed aggression towards those that use them exclusively has come from a number of projects i have worked on recently where it has been blatantly clear that all they have done is add unneeded development time and hamper debugging.

What the old salts of the development world have to say are the reasons for such an approach, are usually one of the following, and I'd like to address them point by point:

Security

Stored procedures are easier to lock down as you can assign a user/group only access to the stored procs and not have to worry about any finer grained control.

It is quite easy to lock down a certain user and their abilities (including showing/hiding items from view) on certain tables, so unless striving for laziness this is far outweighed by the benefits of using a ORM to pull you data and the maintainability and ease of debugging that this brings. Again, only if you want to restrict access on a column level is this required.

Performance

Stored procs are compiled and therefore perform better (old thinking)

Today this is simply not the truth. Frequently used Parameterized Dynamic SQL has it's execution path compiled and cached, giving the little too no speed difference at all.

Quote from SQL Server 7’s literature on MSDN (yes this is a while ago)

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

Once again, the benefits of using a newer approach far outweigh the use of stored procedures in this instance. The only time i can really say performance from using stored procedures is worth the pain, is when you need to move data around the database and don’t want the data to do a round trip to your application and back (less network traffic).

If you're adding stored procs to your application during early development I can almost certainly tell you that your application is probably going to have bigger performance issues in other places, so taking this stance usually means you're optimizing too early at the cost of development time.

Maintenance

If you want to change the way data is stored you only have to change the stored procedure and not the application.

This is true, however if you don't have access to the source of your application and want to change the data it uses one would think that you have a problem elsewhere in your product life cycle. Doing this also adds a layer of abstraction when none is needed making your product harder to maintain in the long run. Their use only makes sense when you have multiple applications using the same database and want to enforce consistency of access.

This point is also a lie in the sense that you're making this call while your application is still in development (i.e. You're saying that you want to be burdened by stored procedure noise just so that if, one day in the future, you won’t have access to the source code and want to change your output data, you can without having to change your application’s code – this is many brands of crazy).

Add to this the fact that stored procedures are crap to debug (have you seen the error messages you get back???) and step, through making fixing applications that have some of their logic inside the database testament to a loong day.

Abstraction

It allows you to hide the schema from an application

This only really applies to teams which have a DBA that likes to play his cards close to his chest, or are wanting to allow outside contractors or developers access to data without allowing them to see the DB - in most teams this doesn't apply. Abstracting the data storage tier from the application is generally never a good idea as it leads to bad application development decisions and un-needed noise.

if you have an internal DBA they should be communicating better and not keeping data storage a black art to the rest of the development team – developers need to know data storage, so that they can better design their applications to use data in an easy to retrieve way.

For the other option, if you want to provide external access to data I strongly believe you shouldn't be doing it at the database level, and instead should provide an external API or web service for that data access as it provides better access control as well as better abstraction.

It helps reuse queries because it consolidates queries into a single place

This is just bloody mindedness is my opinion, as it is all too easy to place all your ORM queries into a single data access class library for easy reuse instead – this can be waaay more easily debugged and tested. This makes it even easier to maintain as you have type safety, so if a method gets removed you break the build - this cannot be said of calls to stored procedures from the string execution of commands (i.e. your app doesn't know if the stored proc exists, it's just a string representation in a command somewhere in your code).

Summary

The above reasons, plus many more are usually hidden behind as reasons to not try something new. None of them apply in most cases any more (ie every project) and hiding behind these arguments and their logic affects your application life cycle in many negative ways. When using modern databases in actual real world development scenarios, I believe the use of Stored Procedure’s and their associated down sides far outweigh what they bring to an application – and therefore in most cases, because their use brings more pain than gain, they should be avoided whenever possible.