[stephenr]

For selects I'd have though having views would be a better solution here.

But it sounds like a lot of overhead to maintain, presuming it's one insert/update/delete proc per table.

IMO stored procedures help where you want to make some reasonably complex logic easier to call into from the application, OR where you want to trigger said reasonably complex logic from multiple DB events.

[Someone]

closing down your database, limiting callers to use stored procedures also helps if your database has more than one stakeholder calling into it.

If that’s the case, you don’t want to allow stakeholders to make arbitrary queries, as those could run fine from their perspective, but would be disastrous from that of the other stakeholders.

Team A could be fine having the database be excruciatingly slow for an hour while they run their monthly reporting queries or while they run a huge import or whole they do a select that forces a table scan on a huge table, but teams B, C, etc might not.

That’s why such a database has a separate team “DBA” whose job it is to know the full picture.

[stephenr]

I don't disagree there are uses for stored procedures - but the question as stated is about CRUD operations, not about complex queries or reporting.

[Someone]

But if you let callers use SQL for CRUD operations, you have to give them the ability to execute arbitrary SQL, and there’s no guarantee they’ll only do CRUD operations.

Yes, you could enforce that through code review, but it’s easier to do by using the access control mechanisms of the database. That also would catch any problems before code review.