Stored procedures were very popular in the 90s and early 2000s. In my opinion, it started to die out because of a couple of trends:
- Companies moved away from having dedicated DBAs
- Software Developers took on database design and implementation
- Writing portable software meant using standard SQL from the application layer to avoid database vendor lock in
- Version control (CVS, SVN, git) was easier to do on application code and hard to do on stored procedures
- Software releases and rollbacks are easier in application code
- Databases are often the scaling bottleneck, moving more responsibilities into the application layer can scale horizontally