A specific advantage .. stored proc can _greatly_ simplify business logic as the business logic lives with the data …
Even without stored proc, a big join can replace tons of application code implementing non-trivial business logic. Hash table lookup can be implemented in SQL (join or sub-query) with better clarity and instrumentation because
* Much fewer implicit complexities in initialization, concurrency, input validation, null pointers, state validity, invariants, immutabilities, …
* OO and concurrency design patterns are often employed to manage these complexities, but SQL can sidestep these complexities.
In fact testability might be the most high-profile feature of RDBMS.
I think one inflexibility is adding new column. There are probably some workarounds but noSQL is still more flexible.
Another drawback is concurrency though there are various solutions.