My previous employer did this, but it was fraught with problems.
It can be done properly, but you need your entire development team to have a good understanding of declarative programming, and you need to have as much business logic embedded in the stored procedures as possible.
Instead, we had developers who continued to think imperatively. Whenever they were implementing a new feature, they would write several small stored procedures to obtain data, and then process it iteratively inside the application server.
The ensuing result was that there was far too much chatter between the application server and the database server than was actually necessary; loading a new web page in the application would take seconds with one single concurrent user, and this was a product that was supposed to scale to tens of thousands of concurrent users!
It can be done properly, but you need your entire development team to have a good understanding of declarative programming, and you need to have as much business logic embedded in the stored procedures as possible.
Instead, we had developers who continued to think imperatively. Whenever they were implementing a new feature, they would write several small stored procedures to obtain data, and then process it iteratively inside the application server.
The ensuing result was that there was far too much chatter between the application server and the database server than was actually necessary; loading a new web page in the application would take seconds with one single concurrent user, and this was a product that was supposed to scale to tens of thousands of concurrent users!