Here's my $0.02
Stored procedures have a bunch of problems historically. Part of this is because the interface traditionally is pretty spartan, and partly because some people take them too far.
The first issue is that if you have a stored procedure which takes 2 arguments and you need to extend it to three, then you have to change every call in the calling application. This can create a maintenance problem. Variadic functions help somewhat but there are limits to what a variadic function can do here. The programs and frameworks I write rely very heavily on argument name and data type detection to rewrite calls dynamically, but that has tradeoffs as well. In general though I think that those tradeoffs are worth it and stored procedures are very, very useful.
The second issue is simply, just because something can go in the database doesn't mean it should. In general people start doing things like sending email from the backend and this usually creates more problems than it solves. The best approach is to see stored procedures as a way to encapsulate the data behind a service-oriented API (like the NoSQL folks advocate ;-) ). Hope this helps.
