I work on a couple of applications with 100% percent of the business logic in stored procedures.
The big downside is tooling is far worse than mainstream programing languages. Version control is doable, but depending on your workflow can be very clunky.
Quite a while ago I worked on two different Microsoft SQL systems that had hundreds of stored procedures, we had a version control and migration system that was ‘good enough’.
You can think of stored procedures like an API that one would provide over http, except it is over the ODBC, JDBC or whatever db interface you use. Surely you could make a web API that is a thin veneer over some stored procedures.
You can do a lot in a stored procedure with the particular advantage of a low latency location close to the database engine for good transaction performance.
I think the devs have learnt this the hard way. it's not about what's technically correct or possible, It is more about getting 10 layers of permissions and signatures from higher ups before you can add or modify a stored procedure. Try doing that in a 2 week sprint and you will never touch stored procs again.
The big downside is tooling is far worse than mainstream programing languages. Version control is doable, but depending on your workflow can be very clunky.