When to use Stored Procedures?

No coding blog would be complete without an overly-simplistic salvo fired into this bloody online battlefield, so here's mine.

What if you're writing an application that will make heavy use of an RDBMS?

If you're writing an packaged app for sale to customers who want to run it on the RDBMS of their choice, don't use stored procedures.

But what if you're writing something bespoke?

Maybe you've never in practice had to change RDBMS in mid-project. But maybe that's because you automatically ruled it out as an impossibility - how do you know what advantages you might have had, what client license bundle offers you could have benefitted from, if you maintained the ability to switch RDBMS vendors on a whim? Competition pressure is what keeps vendors competitive, and competition pressure disappears if customers lose the ability to switch. The vendors know this, which is why they want to lock you in. So if you want to be able to get the best deal out of RDBMS vendors, don't use stored procedures.

But what if you want to have a layered architecture?

Adding more languages doesn't enable more layers. It just adds more development cost. You can write a data access abstraction layer in any language - and in Java and .NET, open source toolkits like [n]Hibernate already make this child's play, largely eliminating the need to generate your own SQL strings or DDL.

If you want to reduce development costs by keeping the number of language skills required to maintain an app lower, by writing all business logic and data layer logic in one language, don't use stored procedures.

But what if you're prepared to take on extra cost because you want to optimise by moving execution into the RDBMS?

If you haven't yet done any comparative profiling to establish where such optimisation is really needed or makes any measurable difference, don't use stored procedures.

... otherwise, knock yourself out!