One danger with overuse of stored procs is unit tests; they're hard to write in sql, and also hard to keep in sync with tests in code. There are also dangers with ninja patching stored procs without corresponding code deploys.
It does make query development much easier to use a database specific IDE for writing the queries versus munging big strings in code.
> There are also dangers with ninja patching stored procs without corresponding code deploys.
This just means that modifying sprocs has to be treated with the same caution as deploments. Unit testing is truly difficult though. It doesn't help that many DBMS products (looking at you, Postgres) don't offer an embedded mode.
It does make query development much easier to use a database specific IDE for writing the queries versus munging big strings in code.