Stored Procedures aren't all that
Now and then I check into The Daily WTF, partly because it’s funny and partly because it makes me feel a little better about some of the crazy code I’ve observed or been asked to maintain.
One in particular from about seven years ago amused me greatly, but not just in the train-wreck sense for a change. It addressed the very serious subject of putting all your database code into stored procedures. The comments are actually pretty illuminating.
Alex Papadimolous, actually being serious, wrote:
Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the “data layer” by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality.
I don’t think so, if by “most information systems” you mean the typical multi-tier business and financial databases that most of us maintain in our day jobs. I find that the looser coupling is almost always theoretical; it breaks down when you have to look at code from a year or two ago and figure out what the underlying stored procedure is actually doing instead of what you expect.