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.