Stored Procedures vs. ORM

I was recently asked to give an explanation of my preference toward use of stored procedures vs. an ORM solution which happens to be a question that I have considered several times during development myself so I thought I’d throw in my 10 cents, so to speak.

Listing each of their respective benefits is not something I intend to do as there’s plenty out there in the developer community doing just that. Looking about you can find a couple of detailed post on StackOverflow.com (here and here) that describe some of the advantages and disadvantages of each solution. There was a response on one of those threads from an individual who came from a database background who was leaning toward ORM generally for reasons around the simplicity of certain operations and the reduced time in writing them. As for me, I have a tendency to lean a little the other way - whilst I agree with the reduced development time associated with an ORM solution I like the enforced separation of concerns imposed by accessing the database just through stored procedures (although I appreciate that if you’re implementing the repository pattern then there is a separation imposed there).

Knowing also that stored procedures can do much more than perform CRUD operations and queries plays a part in my opinion. Enterprise applications can benefit from stored procedures performing operations that ORM was never designed to replace like distributed transactions, message queuing etc. and remember: stored procedures can be changed\tweaked without requiring a re-release of compiled code.

In my opinion then, the factors that come in to play are speed of development vs. ease of update - and I’d go with ease of update FTW. That means if you’ve got a database team I suggest you use them.