How do you write SQL in your projects?

Question

Programming

Good day.
In my project, I decided to use Dapper. Very often there are situations when you need to change the SQL query depending on some variables.
For example:

var sql = "SELECT field FROM table";
if(someModel.IncludeSomething){
   // добавить inner join something + добавить SELECT something.field
}

if(conditionN){...}

Using a stringbuilder or string concatenation is very inconvenient and, in my opinion, few people do that.

Answer 1

or string concatenation is very inconvenient and, it seems to me, so few people do.

Well, in vain.
Concatenation and format string are everything.

Answer 2

In general, stored procedures and, firstly, it is not necessary to dynamically generate new queries for the sql server with the risks of errors and all sorts of holes. And for sql procedures, the server stores execution plans and other goodies of optimization.

Answer 3

String interpolation ? No, have not heard. This is one option. Back when I was working on one large enterprise project on ASP.NET MVC, we stored requests in the resource files of the project. And then there Visual Studio very conveniently generates a class in which these strings from the resource are stored, you pull them String.Format(MyResourse.MyQuery, var1, var2). Those. as in the first option, but not to store SQL in C# code. And finally, of course, you can always use storage and orms.

Answer 4

In python, I just do the necessary operations on the query string. I believe that you should form the structure of the database and queries in such a way as to fit into them as little as possible, while separating as much as possible the functions performed in your PL and the functions performed by the SQL server. For example, if something can be checked directly in the request, you can do it in the request by downloading the result already.
If the query needs to be changed a lot, maybe it's better to use two different queries, optimizing each for its task?