As a fan of SPs:

They offer a clean and easy (IMHO) way of making sure I've got the exact same code for common jobs encapsulated and tracked within the database. One routine to add a user (for example) which is common to all client interfaces, handles all the exceptions in exactly the same way, tracks all the security in exactly the same way. I've had my share of problems where you get an odd bug because the app has subtly different SQL for the same job in different places, which just don't happen with stored procs.

In theory you can get this benefit from any common code repository and indeed, where SPs haven't been available, I have written code with a common SQL cache held outside the database. SPs have the advantages though of being incredibly easy to update - no need for a build and deploy, you can correct the code on the live server without disturbing it almost immediately if you need - and of doing so in a way that is then fully trackable by the database itself. I can easily query the information schema to find all procedures that reference a certain object that haven't been updated in the last week, for example.

They've also got security benefits. It's common practice in some areas to lock the tables down completely and restrict access to purely through SPs by permission, which can offer major benefits because you've got far greater control over what user accounts can do what to your database.

They're not perfect and I agree they're not portable (though in practice I've needed that very, very rarely) but I find the benefits hugely outweigh the downsides and will pull a face if told not to use them because it's simpler just to treat the database like a slightly funny version of Excel (or words to that effect, which I have had and don't get me started...!)

One application is to allow very fast patching. If we need to patch an issue and our query is in code that will take N minutes to update, perhaps compile, and push out to every web server, it can be a tiny fraction of the time if all you had to do was update the SP in the DB server(s).

I can do an alter proc with literally seconds of overhead (beyond the obviously required time to actually change the SQL). If it doesn't work, or makes things worse, I'm seconds away from reverting or taking a second bite at the apple. If your SQL is in the app on your web servers, most places running at any sort of scale can't match that.

(as a result of being heavily [99.8%+] SP based, we've had to develop some compensating technology to allow us to do releases without downtime when each release needs its own specific SPs. Solved, but took work. We're also predominantly MS-SQL with limited mySQL, but I doubt that changes much.)

If it's not a proprietary secret, can you elaborate on the compensating technology you guys developed?

I prefer encapsulating database logic in SPs as well, and am always interested in learning how others have solved some of the problems incurred with them.

I've written about it briefly before on HN, and it's very beneficial but not proprietary tech. Basically we create a "shim" database containing our app's sprocs, views and functions, and additionally views to the transactional database tables in another database.

That means that release N can be pointed at the "real" database, while release N+1 can be pointed at the shim DB and they're both using the same transactional data. You can run both in parallel until you commit to N+1, whereupon you shutdown all N app servers, update the sprocs, views and functions in the main DB, and (optionally) point your web servers back at the main DB. There are a few other details to take care of, but that's the gist of it, and the details are minor, or at least we found them to be.

Disclaimer: the above is my experience on MS-SQL at a pretty good sized eCommerce site. Other RDBMSs may not work quite as well, or your app may use heavily features that don't work in "shim mode".

Ah, very cool, thanks.