There has always been a need to put something between raw data and UI, the question is always "what". You could rely on some sort of persistence framework or other O/R mapping solution, but frameworks have proven to be more transient than stored procedures, meaning custom solutions are more future proof, whatever they may be.
The concept is still alive and well and the most popular modern solution is micro services, which essentially mirrors that of stored procedures going after data. Stored procedures are a more efficient way to manipulate and aggregate data when working with multiple database tables in a single transaction because the language is optimized for data, and avoids network round trips, so it's not unreasonable to use stored procedures as a back end to web services today. It does require another language in the stack, but the days of single-language applications are long gone. There is a risk that stored procedures get over utilized, but those decisions are left up to seasoned application architects.
That's the long way of saying yes it still should be a valid choice that is often overlooked, but the other trend today is purposefully decoupling the data layer from the application to enable flexibility to swap into the next latest and greatest database technology. Such future-proofing automatically eliminates significant stored procedure use, so it all becomes a tradeoff. Keep in mind that popular databases tend to be around for decades.
As a side note, DBAs are traditionally the equivalent of system admins who have little software development background, so I'd be a little careful assuming a DBA is needed for stored procedures. On the other hand I do see a common trend to not hire anyone with significant database experience. When no one on staff is any good at database design and optimization, you're bound to run into tuning issues at a minimum, or significant refactoring at worst (and I've seen application refactoring costs in the 10 figure range).