When is it better to write queries directly in the code, and when is it better to put them into procedures?

Question

SQL

I am making a WCF service that should pull data from MS SQL and respond to client requests and was puzzled:
When is it better to write queries directly in the code, and when is it better to put them into procedures?
I tried to find some patterns that give recommendations, but I didn’t find ...
I identified 3 possible solutions and their problems:
-If you write everything in a row in the code, then the code will be, in my opinion, dirty, everything that is not included in the specifics of the language.
-If you write everything in procedures, then there will be a lot of platitudes in the procedures (Select * from User).
-If you write elementary queries in the code, and put complex queries into procedures, then, probably, the code will become harder to maintain. There is one more problem: how to separate elementary queries from complex ones? Is a 4 JOIN construction considered complex?
I, personally, lean towards option 3, but are there any conventions for determining the complexity of queries?
How do you decide what should be put into procedures and what should not? What is recommended to be left in the code?
I myself plan to use the Data Mapper pattern.
I don’t plan to use ORM (If only a dapper, for the convenience of using queries), since it is good in itself somewhere, but when it comes to connections, it looks terrible, and the query itself may not be optimal to be compiled, in my opinion, a pure SQL query looks the most beautiful.

Answer 1

Learn to use the Entity Framework - it's the standard way to work with databases in C# these days.

Answer 2

I myself plan to use the Data Mapper pattern.

This is the ORM.
Take out functions that do simple operations on fields.
The less logic in the base, the easier it is to maintain the project.

Answer 3

The problem is the following. Suppose now your query is simple and boils down to a single SELECT or UPDATE. Six months pass, a couple of entities are added, and this is not enough - as a result, you rewrite the server code. When using stored procedures, this situation is excluded - you rewrite the stored procedure, and the server code remains the same.

Answer 4

Only queries are taken into procedures where it is necessary to immediately use the values ​​of previous iterations of this query: descending the set tree (your 4 JOINs), filtering, recursions, searching for intersections by criteria (data-mining).
Everything else is better to use directly in the code.

Answer 5

I am personally a supporter of the fact that requests should always be carried out in procedures. Those. in general and absolutely. And there are only two arguments, but they are very weighty if you think about it:
1. The user does not have direct access to the tables. In the case of requests in the code - you have to decide something with this, right? Either give rights to everyone, or come up with some kind of common login, but then it’s not easy to track who and what changed in the database.
2. Precompilation of requests, work with statistics. If requests are not made in procedures, then they will be compiled before execution - and so every time. Banality in procedures is not a problem.
Well, the last. Suddenly there is a decision to remake the code to another language / system. If all the server logic, as expected, is on the server, there will be much less rework than if you climb all over the code and rewrite it again in another system.