Having to deal with many customers and projects, sometimes it still happens to me to have to deal with projects that implement CRUD operations with Stored Procedures in SQL Server.

Performance

This pattern was more common many years ago, when stored procedures were precompiled by the database servers and they got a dedicated cache for their execution plans.
But I am surprised that dedicated SQL websites are still on this old idea, for example see this SQLShack article, under the Performance section.
In fact, in the same website, (in another page)[https://www.sqlshack.com/understanding-sql-server-query-plan-cache/] it shows exactly the opposite: as long as the query text doesn't change, the cache area is shared between stored procedures and plain SQL statements.
Clearly you must provide always the same query text and eventually use SQL parameters when necessary.
So: if you follow this basic rule, stored procedures don't give additional performance benefits.

Prevents SQL injection attacks

The same SQLShack article states that "Using stored procedures... reduces the chance of SQL injection attacks...".
The meaning it's all in the reduces word: with stored procedures you don't eliminate this danger of SQL injection attacks and even worse, you should not have the mindset that with stored procedures you are safe and so underestimate the problem.
Clearly, for SQL queries, you are strongly suggested to use SQL parameters, both for performance reasons (see previous point) and because they are automatically escaped, protecting you from SQL-inject attacks.
So: SQL statements, if called with proper parameter passing (and not string concatenations) has the same level of injection hardening of stored procedures. The variables are treated as isolated values and can't break out.

Decouples the SQL code from the other layers of the application

The final point from the SQLShack article talks about the supposed benefit of decoupling SQL code from the other application layers.
About this I would like to make a precisation: typically CRUD operations are not feature rich and you DON'T want to separate it from the other application layers.
Think about CI/CD scenarios, where you may want to retract an application version deployed to any Test/UAT/Prod environments: if an application is deployed together via stored procedures, you will need also the retract them as well.
So: in the case of CRUD operations, the use of stored procedures is in my opinion a definitive disadvantage, because it adds complexity without providing any added value.

Conclusions

So, are stored procedures to be banned?!? Absolutely NOT!
There are multiple cases where the use of stored procedures is suggested, and I can provide here a few examples:

• When there is a batch operation that would consist of multiple SQL statements, and for which the roundtrip to the application server doesn't give any additional information. In this case, a stored procedure would be more efficient, because the network roundtrips would be avoided and the execution could happen completely on the database server;
• When there is a batch operation that is really decoupled from the main application, and whose deployment scenarios are mostly independent from the deployment of the application(s).

Probably stored procedures for CRUD operations came from long time ago, when it was very expensive to write and deploy application. And a stored procedures worked like a library for all applications that connected to the database. Also sometimes certain application were not allowed to do queries at all, but had to do everything with stored procedures. They could also call other services and trigger general database functionality.

Performing CRUD operations with stored procedures, now the risk is that the database developer becomes the bottleneck of development (usually there are many less resources assigned to database development), as well later the database engine becomes the bottleneck during runtime execution.
For both flexibility and scalability, you don't need to implement CRUDs via stored procedures.

In conclusion, stored procedures are tools. You shouldn't use stored procedures for everything (i.e. CRUD operations), but you shouldn't avoid them as well: use them (only!) when you really need them!