Stored Procedures Followup

As a follow up to my previous post that stored procedures (sprocs) aren't a best practice  (http://scottwhite.blogspot.com/2008/04/stored-procedures-reconsidered.html).  I would go as far as saying that as a general rule for database stored procedures are a bad practice.  In general it's hard for the brain to go from one extreme to the other, so as obvious as this may be for some, it is hard to accept for others.  The basis of my argument is to counter each of the positives of stored procedures followed up with the advantages of not using them.

If you are a believer in sprocs as a general rule then you should still come up with some disadvantages to make an impartial decision.  Early June ('08) I was at TechEd and I reiterated my blog regarding stored procedures to a member of the SQL Server development team at Microsoft.  Afterwards I asked if this was correct in his opinion.  His words: "Yes, I tell people not to use stored procedures."

To which I responded "it would be nice for you to tell people this."

"People are going to do what they are going to do" was his response.  My translation is simple: the opinion of your peers do not necessarily reflect the best practices of the vendor.  Additionally times change, best practices change, performance metrics change and Microsoft doesn't typically speak with solidarity when it comes to best practices or design decision.  i.e. as long as you are running on Microsoft platform with Microsoft language and tools they don't care what you do.

At one point in time it was sufficient for systems to compile into each other's code shared header files but this is no longer acceptable and we require packages or assemblies that are more portable such as a DLL.  You have to accept that change is inevitable in life and prepare yourself for trends that you may be uncomfortable with or unaware of.  I'll close with a comment from the creator of Hibernate.

Stored procedures are essentially a nonrelational view of a relational database ... my view, currently, is that the goal of an object-relational mapping tool should be to map between tables and objects, not between objects and "some other stuff."