It’s a good point, I remember from working in Oracle DB , we’d almost always rather use parametised queries in a DAL rather than stored procedures, the only exception was our accounting module, where the code was considered too complex for the average developer, and most developers there had rights to change the DAL, but not to change stored procedures. Weird setup I know.

If you look at something like LINQ, it’s a lightweight ORM in a way, you can see MS is looking outside of stored procedures.

Personally? I like them. It gives you a nice insight into what’s happening at a database level, i.e. database operations happen at a database level. From a performance point of view though, there are cases where constructing a statement will yield a much better performing solution than trying to write either a generic sp, or multiple sps for each use case.

From an installation and maintenance point of view, source control for compiled code is easier than maintaining SPs, there’s a plus point. That’s just management practise, but still. Releasing a new app to a client with an installer is easier than building an installer that archives the old SPs (for uninstallation) and installs the new one. I’ve done it, and it’s possible, even easy, but it’s not completely trivial / automated, and is time consuming.