Tim Holloway wrote:
I don't recommend using stored procedures as a general rule. There's no standard for them. They vary considerably between different brands of databases. And if you don't think that's a problem consider that Amazon used to use Oracle extensively until last year, when they completely switched over to a different DBMS platform. Straight SQL is easier to transport, especially if you're using an ORM to front for it.
Maintaining source code control for stored procedures is also a problem. Often there is little or no versioning available for the code stored in the DBMS and tools to work with code versions can be hard to come by.
Use of stored procedures in application programs can also make the applications harder/more expensive to maintain, as you may end up going on "treasure hunts" to find out what logic is in the app and what logic is in the database (stored procedure).
So when should you use stored procedures?
Well in some cases, you cannot do what you need efficiently using plain SQL. In cases like that, code that runs entirely on the server may make it worth the disadvantages I outlined above.
Another instance is when you have a critical business process that operates according to a complex set of rules and you have many applications using those rules. In cases like that, it's certainly more efficient to keep one set of rules (the stored procedure) rather than having to maintain multiple copies of them.
Still another case would be where you have users who use some sort of command-line utility to issue database commands directly such as sql*plus and you want to make those commands simple and easy to use.
So there are some good reasons for using stored procedures. But try to keep them to a minumum. Because, as I said there are some very good reasons not to.