Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible.
"Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s."
Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.