calling a truce on sprocs

For a while, I've mostly been dismissive of database stored procedures or sprocs. The rationale is that databases are for storage ("Really, Capt. Obvious?"). By contrast, calculations, conditions, and data processing in general belong in a separate, dedicated tier; the clear benefit is a much more flexible, capable, reusable, and interoperable platform/language than the typical sproc. In this middle tier the intelligence resides in neatly divided objects that could potentially exploit different "persistence strategies" than the default database of choice. These objects presumably act as better models of the domain than collections of rows and columns. Application development happens on top of this middle tier rather than the database.

The opposite path is integration at the database level. Differing software all use the same "master" database. There may be a recurring import script that populates one or more tables with external data, entry interfaces that quite clearly manipulate rows and columns, canned reports whose queries become increasingly complicated. Knowledge of which tables to join or which column values to exclude spreads out through everything that performs a similar task. Analysts speak of the database as if it were the domain. Their first implementation question on new projects is "What tables do we need to add?" 

Consequently, integration at the master database level can result in fragmentation and duplication. Enter sprocs. Essentially, a thoughtful agglomeration of limited and self-contained sprocs could take the place of a nonexistent middle/domain tier for some purposes. If everyone needs to run the same query all the time, at least putting it in a sproc will consolidate it. A complex calculation that everyone repeatedly makes could be computed in a single sproc. Ugly warts of the database model could have workarounds specified in sprocs.

Storage technology independence is lost with sprocs, but ongoing integration at the database level already makes that impossible. Sproc writing requires some learning but is offset by the considerable advantage of not having to rewrite the code in multiple clients. IDE support is less than ideal but a sproc shouldn't be too large anyway. Names and calls of sprocs are also rough but are likely to require less extra documentation than the alternative of laboriously touring table relations.

Sprocs: better than nothing.