SQL dynamic search query

Question

I am working on a problem that I am sure someone has seen before, but everything I found on the network was not right.

An example of a fake table and dynamic search . (Due to my low rating, I can’t post images. I know I should be ashamed!)

Pressing the add button automatically creates another line to add additional criteria.

(Note: My table is most definitely more complex)

Now, to my problem, I thought I knew how to handle SQL for this task, but I really do not. The only examples of what I should do are not for this kind of dynamic table queries. In the examples, it was not possible to create as many search filters as the user liked (or perhaps my understanding was missing).

Please let me know if my uploaded image is not of sufficient quality or I have not received enough information.

I am really interested in learning about best practices for this situation. Thank you in advance.

Answer 1

. sql sp_executesql, proc, select .

proc, , , where, NULL .

proc, : SQL ?

, , , SQL-, SQL.

Answer 2

:

Linq to Sql , , , SQL, .

SQL. "WHERE (1 = 1)", , ( SQL-) SQL-.

Answer 3

SqlBuilder, Dynamic SQL.

Answer 4

: sql

select * from thetable
where (@name='' or [name]=@name) and (@age=0 or age=@age)

However, the above query forces a table scan. For better performance and a more complex scenario (I think you simplified the question in your original post), consider using dynamic sql. By the way, Linq to SQL can help you build dynamic SQL very easily, for example:

IQueryable<Person> persons = db.Persons;
if (!string.IsNullOrEmpty(name)) persons = persons.Where(p=>p.Name==name);
if (age != 0) persons = persons.Where(p=>p.Age=age);