Stored procedures have been falling out of favour with some organizations for several years now. The preferred approach of these businesses for accessing their database(s) is to employ an Object-relational Mapper (ORM) such as NHibernate or Entity Framework. Over the next couple of blog articles, we'll explore their reasons for doing so, and whether this paradigm shift points to the eventual obsolescence of Stored Procedures.

Stored Procedure Basics

As expressed in the Understanding Stored Procedures and Functions in Relational Databases:

A stored procedure - or "proc" for short - is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. Stored procedures can access or modify data in a database, but it is not tied to a specific database or object. This loose coupling is advantageous because it's easy to reappropriate a proc for a different but similar purpose.

Sounds like a useful tool so far, but, as we'll see in the next section, not everyone is convinced.

Drawbacks of Stored Procedures

Despite their long-established advantages, opponents of Stored Procedures point out their many disadvantages, such as:

• Bug Prone: Since stored procedures encapsulate application logic, it should be moved into the application code, where it can be better managed and tested. Due to the inherent challenges in testing stored procs, they can be the cause of some really nasty bugs.
• Implementation Differences: Stored procedure implementations vary from vendor to vendor. While many DB developers consider Oracle's stored procedures to be of the highest quality, other products' procedures, such as those of MySQL, are less well conceived.
• Changing Requirements: One of the original use cases for stored procedures was to reduce network traffic. However, with today's lightning fast network speeds, this isn't nearly as big an issue as it once was. As such, dropping application logic into stored procedures can be a case of premature optimization.
• Difficult to Maintain: Stored procedures tend to require much more work to develop and maintain than application code. For starters, you need to have individual stored procedures to execute create, retrieve, update and delete operations for each table, plus a separate stored procedure for each different query that you wish to make. Then, you need to implement classes and/or methods in your code to call each stored procedure. Compare that with an O/R mapper, where all that's needed are class definitions, database table, and mapping file. In fact, modern ORMs use a convention-based approach that eliminates the need for a separate mapping definition.
• Code Duplicaton: Stored procedures require you to violate DRY (Don't Repeat Yourself) principle, since you have to reference database table columns half a dozen times or more. Moreover, it isn't possible to pass an object as a parameter to most stored procedures - only simple types like string, integer, date/time, etc. - making it virtually impossible to avoid huge parameter lists (a dozen or more is common!).

Even the most staunch opponents of stored procedures still use them in some circumstances. For example, stored procs are great for database housekeeping or reporting. Otherwise, developers should have very good reasons to integrate them into their applications.

Going Forward

Having heard a few reasons for eschewing stored procedures in favor of application code and Object-relational Mappers (ORMs) such as NHibernate or Entity Framework, you may be convinced that this is the way to go. Well, don't make the switch just yet; in the next installment, we'll consider a few more motivations for both abandoning and staying with stored procs. Then, if you still want to make the change, at least you'll be armed with a more complete understanding of all the issues involved.