Impossible to generalize, would depend entirely on what specifically you want the stored procedures to do. Though I can’t think of a good reason to abstract your queries at the database level. Even ORMs typically allow execution of raw SQL, so you could still store a straight SQL query in your app code (perhaps in a constant) instead of using a stored procedure.

I've been thinking about this a bit before seeing this discussion, and I'm starting to feel that abstracting at database level makes sense, because it turns your RDBMS from being just a dumb SQL execution engine into an application-specific API.

If you consider restricting operations on your data close to the storage layer, you can imagine wrapping DB access with an app exposing business-specific API, and route all your actual code through that API. Doesn't seem like an unreasonable design to me - particularly, if the same database is used by multiple applications. But if you do that, it may make sense to just put the API inside the RDBMS - giving you one networked software component less to manage.

(I've done work on a project using lots of stored procedures only once, and my tasks were unrelated - so I have no practical experience here. But through this discussion, I think I'm starting to understand why enterprise projects are anecdotally so in love with stored procedures.)

If you abstract at the db level then your db reading code is either unlikely to be in version control or you need a klug to for version control for stored procedures.

But that's a concept entirely orthogonal to system architecture and solvable with appropriate process and/or tooling.

I suppose it is in theory but how many of us work in theory rather than with a set of tools that are commonly available?

Or maybe it’s just better to roll your own version control system to support your really amazing new app architecture that stores lots of logic in database functions, what do I know? ¯\_(ツ)_/¯