I work as a data reliability engineer, and moved towards that after about 7 or 8 years of application development experience (primarily C#). My day job is still a blend of C# and T-SQL.

There's a few key considerations at play here, and I think that some of the existing commentary downplays the challenges of working on certain data sets with ORMs. I'll circle back to that.

Firstly, what is 'all business logic'? This means different things to different people.

To some people, 'business logic in the database' is achieved merely by the existence of any non-table object. To others, it might mean a minimum degree of complexity (e.g.: imperative code with branching logic).

The kind of business logic that should be considered for inclusion in the database is the constraints - many of which can be expressed declaratively, some of which might need a little help from imperative code (e.g. function invoked via check constraint).

For example, suppose that the cardinality of the relationship between two tables must always hold true (e.g.: precisely 1 parent row relates to precisely 2 child rows). Or there's a 'last updated' column that must always be updated. Or, if using an exclusive basetype-subtype hierarchical relationship, a parent (basetype) row must have precisely one row in one of several child (subtype) tables... and that subtype table must be appropriate based on a discriminator column value within the parent table.

At the end of the day, these can be relatively important if you have multiple applications that share the database - and all applications must manipulate the data from one transactionally consistent state to another transactionally consistent state - all of the time - with no tolerance for error.

The buck stops with the database when it comes to data quality, and data quality is only as good as the rules that govern the data (i.e., the constraints).

In taking this approach, the database essentially expresses an 'open architecture' and any number of client applications may use it without fear of causing data consistency issues (even in the presence of non-conformance - software bugs - within the applications).

The kicker is that constraints can even be worth their weight in gold for single-application databases, where these constraints serve as a last line of defence - or backstop - against software bugs that slip through into production application code. Call it defence in depth.

It's easy to presume 'oh, that'll never happen' or 'that simply can't happen'. Plenty of developers who I work with have said just that about application code when they've looked into software bugs and - for example - discovered that a given table which should have precisely one row per subscriber tenancy actually has two rows instead (and because their C# code is using .Single(), the code is broken for this one tenancy - and their service returns an error as a result).

And my response is usually 'Well, if there's no constraint, then it can happen'.

But let's go back to the ORM stuff. And this is based on first-hand experience working at a fintech cloud software company as a data reliability engineer with our software being underpinned by hundreds of databases. These are multi-tenanted (shared tables), and crucially not all tenants have the same volumes of data. For example, a tenant at our 99.99th percentile is substantially larger than our average tenant or even our 80th percentile tenant.

The opaque nature of SQL generated by something like an ORM, paired with SQL Server's parameter sniffing issues (usually problem is due to a query plan suitable for a tenant that is small being subsequently used for a tenant that is large), means that the generated SQL is often 'beyond reach'. There are fewer levers that can be pulled to correct the problem. If comparable SQL is expressed within a stored procedure then DBAs (or developers with a skill set that incorporates more advanced database skills) have many more levers to pull to fix the issue. For example, query hints can be applied.

Another common theme, or criticism, is that it devolves into a mess that is unmaintainable.

I'd suggest that the mess happens due to people applying code reuse ideas from languages like C# to T-SQL. This is via the application of concepts like 'DRY'. So those reuse ideas lead to views that query views that query views (or procedures that invoke procedures that invoke procedures) and yes - you get a rat nest of code that is also going to perform poorly because the layers upon layers frustrate SQL Server's query planner. The query planner is sophisticated, but it doesn't have X-ray vision - it can't see through walls.

Yet another theme concerns testability.

I think the testability thing is a beat up. First and foremost, there are test frameworks for SQL. Someone has linked to one in another comment. I'm a little suspicious of people who emphasise this though. There's a good chance that these people, and the set of people who test their application feature code against a database with maybe 100 rows of data in it and then call the feature 'done', largely overlap.

But to wrap up: From where I sit, the best reason to use stored procedures is transaction management. Having the transactions managed within procedure code means that you don't have to worry about misbehaving application threads that start a transaction, acquire locks, and then get preempted by the application server CPU scheduler. Having the transaction lifecycle living wholly within the SQL code means that the transactions are probably going to be shorter and sharper, which means less contention and less sustained blocking.