The following is from Scott W. Ambler's white paper on mapping objects to relational databases. Scott W. Ambler is the author of The Object Primer, Building Object Applications That Work, Process Patterns and More Process Patterns.

He has the following to say on using Stored Procedures for CRUD operations in an OOP application:

A stored procedure is basically a function that runs on a relational database server. Although SQL code is usually a major component of a stored procedure most database vendors have their own proprietary programming language, each with its strengths and weaknesses. A stored procedure typically runs some SQL code, potentially massages the data, and then hands back a response in the form of zero or more records or as a database error message. Stored procedures are a very powerful feature of modern relational databases.

When mapping objects to relational databases there are two situations where using stored procedures make sense. First is when you’re building a quick and dirty prototype that you intend to throw away, assuming that you don’t have a solid persistence layer (Ambler, 1998c) already built, then this is most likely the quickest way to get your prototype up and running. The second situation is when you’re mapping to a legacy database whose design is completely inappropriate for objects and you aren’t able to rework it for your specific needs. You can create stored procedures to read and write records that look like the objects that you want. Note that you don’t need to write this code using stored procedures, instead you could do it in your language of choice and run it outside of your database (although perhaps still on your server machine to avoid unnecessary network traffic).

There are, however, several reasons why you don’t want to use stored procedures when mapping objects to relational databases. First, the server can quickly become a bottleneck using this approach. You really need to have your act together when moving functionality onto your server – a simple stored procedure can bring the server to it knees if it is invoked often enough. Second, stored procedures are written in a proprietary language, and as anyone who has ever ported between database vendors, or even between database versions from the same vendor, this can be a show-stopper. The one thing that you can count on in this industry is change, and you can count on at least upgrading your database in time. Third, you dramatically increase the coupling within your database because stored procedures directly access tables, coupling the tables to the stored procedures. This increased coupling reduces the flexibility of your database administrators, when the want to reorganize the database they need to rewrite stored procedures, and increases the maintenance burden of developers because they have to deal with the stored procedure code.

The bottom line is that stored procedures are little better than a quick hack used to solve your short-term problems.

**************
Comments?