Give preference to writing queries on the application side instead of writing stored procedures.
The following are some factors that hang on the side of writing queries on the application side instead of writing on the database side.
For the database, there is no difference between executing queries on stored procedures or received from the client.
You will only be able to observe a minimum performance difference between one and another due to environment variations that are very difficult to control, and sometimes stored procedures will be slightly faster and sometimes the queries sent by the client will be slightly faster .
Where stored procedures can take advantage of heavy data-heavy processing, for two reasons: because you are closer to data and do not need to traffic them over the network, and because you can easily rely on disk usage physical and temporary tables so that you do not need a huge amount of RAM.
So if you do not plan to do heavy data-heavy processing on stored procedures, you can discard the performance factor of your decision making.
See, for example, results of this measurement .
In addition to the complexity of an additional language, there is the complexity of additional tools, such as code debugging. In addition to the tools for C # are much more evolved than the tools for Transact-SQL, for example.
Project structure, dependencies, tracking, versioning, deploy ...
All of these needs are more complex when it comes to database code than when it comes to standard application code. Luckily, table definitions vary much less than all the rest (business rules, queries, UI ...). So using database code for queries or business rules increases the code management, distribution, and application update challenges.
When there is a need to process large volumes of data and choose stored procedures, another major problem usually arises: scaling the solution.
The demand for a lot of performance usually has no end - data volume only grows and yesterday's solution no longer meets today, and database servers are recognized for not allowing horizontal scheduling. You put more and more processors on the server but find that it costs a lot more than adding a new server - not all database servers support parallelism between physical machines (or "active cluster"), and those that support are expensive, far more expensive solutions than just buying one more simple server or allocating one more machine in the cloud.
I have seen projects migrating the processing to the database (stored procedures) and a few years later migrating back to the application side, and the two movements had the same demand: more performance.
The SQL language is quite standardized between banks, while the language for writing stored procedure is very specific in each of them. So it's easier to support more than one database by writing only SQL instead of writing stored procedures as well. By "easier" it means less costly and less risky, since each query only needs to be written once while the stored procedure needs to be rewritten for each database.
Of course this factor is only important if you want to support more than one database server, which is a fairly common requirement.