Not having consistent rules about what code belongs where is a people problem, not a technology problem. In order to write maintainable software, you always need proper code classification, regardless if you use stored procedures or not.
Again, this has nothing to do with technology. Why do we assume that you only have to follow software engineering principles when writing Java, but not when writing SQL?
I’m not sure what you’re arguing. You should have rules for what code goes where and stored procedures are also considered an anti-pattern by most developers because they tend to split business logic between the application code & database.
Let’s say you decide to use stored procedures with rules about what belongs in app code and what can go in a stored procedure, the “discipline doesn’t scale” perspective would say that as your company and app code gets larger your rules get harder to enforce. So devs tend to fall back to the simplest, easiest to follow version of the rule: stored procedures are an anti-pattern.
So what would be a solution then? I imagine, creating an RDBMS where SQL can be executed only by DBA root accounts, and normal (app) accounts can only execute stored procedures? That would enforce a pattern without requiring discipline.
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.
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? ¯\_(ツ)_/¯
