Stored Procedures are a blight on the Earth.
Seriously. There is a place and a time for almost everything. But too often, SQL stored procedures are used when and where they shouldn’t be.
I advocate stored procedures when:
1. You want to codify extremely complex processes as fundamental database operations.
2. You need to do something that requires a lot of processing within the database context and the benefit of reduced network activity and frontend calculations compensates sufficiently for the fact that you’re adding to the workload on the database server. And the more frontend processors you have the more justification you’ll need. Database boxes are usually more expensive and often require additional expensive database licences as well.
3. You need to present a simplified command-line style query to nontechnical users.
4. You’ve got to execute the same business logic from a variety of different client platforms, such as ASP.Net and JEE.
In exchange for the above benefits, you pay the following costs:
1. More load on the DBMS server machine (as previously mentioed)
2. Less portability in the SQL code of the frontend apps. It can be a major pain to convert an Oracle PL/SQL app to MS-SQL or vice versa, but most of the ORM tools can switch the bulk of simple SQL without even recompiling the app.
3. Fracturing of the application structure. This one bit me really bad last month. I got an app where you literally couldn’t tell what the Java code was doing because half the program logic wasn’t even in the source code tree – it was in stored procedures on someone else’s database.
4. Related to item 3, anyone doing design or maintenance work on the apps will need fluency in not 1, but 2 languages. One for the frontend and one for the particular dialect of database in use. One of the major selling points for ORM is that it’s easier (and cheaper) to find people fluent in Java than it is to find Java/SQL experts.
In short, I’ll use stored procedures when I have a compelling need, but that’s rare. It’s a convenience with a very high price tag.