I think part of the issue is that people tend to consider stored procedures part of the application's internal implementation where you just change all the call sites when you change the function.
Normally stored proc are really more like a library API - something that's a bit of a pain to change due to asynchronous updates of apps and interface, multiple interface users, etc.
I think the above is just about exactly right. Also the fact is that since this is communication across a network usually, asynchronous updates of apps can be more or less a given.
If you think about them that way the question "should this be done in apps or in a stored proc" must be asked for each individual procedure.
I would actually say it is worth stepping back from that and asking "what do I want to get out of stored procedures anyway?" and building logic in the application to make sure that happens.
For example, in LedgerSMB, we adopted a stored procedure-centric approach. We decided to follow certain conventions in argument naming, and have the application look up the arguments before the procedure call. Thus if the function is redefined, the new version is used, and the API discovered at call time.
Each approach has tradeoffs however. Our approach works great for what we do with it, but it has some significant costs including the fact that this approach is incompatible with function overloading since the name is the discovery criteria.
If you have other needs, a different approach may be helpful. However it really is imperative to sit down and look at the design questions.
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
