SQL Dynamic query for searching

Question

I am working on a problem that I'm certain someone has seen before, but all I found across the net was how not to do it.

Fake table example and dynamic searching. (Due to my low rating I cannot post images. I know I should be ashamed!!)

Clicking the add button automatically creates another row for adding more criteria choices.

(Note: My table is most definitely more complex)

Now to my issue, I thought I knew how to handle the SQL for this task, but I really don't. The only examples of what I should do are not meant for this sort of dynamic table querying. The examples didn't have the ability to create as as many search filters as a user pleases (or perhaps my understanding was lacking).

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

I'm really curious about the best practice for this situation. Thank you in advance.

Answer 1

Two main choices:

Linq to Sql allows you to compose a query, add to it, add to it again, and it won't actually compile and execute a SQL statement until you iterate the results.

Or you can use dynamic SQL. The trick to making this easy is the "WHERE (1=1)" technique, but you do have to be careful to use parameters (to avoid SQL injection attacks) and build your sql statements carefully.

Answer 2

Check out this utility for Dynamic SQL: http://dbextensions.sourceforge.net/sqlbuilder.html

Answer 3

http://stackoverflow.com/questions/2770482/write-a-sql-for-searching-with-multiple-conditions/2770512#2770512

Answer 4

I had a similar question. You can use dynamic sql with the sp_executesql stored proc where you actually build your select statement as a string and pass it in.

Or you might be able to write a stored proc kinda like the one I created where you have all of the conditions in the where clause but the NULL values are ignored.

Here's the stored proc I came up with for my scenario: http://stackoverflow.com/questions/1317368/how-do-i-avoid-dynamic-sql-when-using-an-undetermined-number-of-parameters/1319140#1319140

The advantage with the parameterized stored proc I wrote is that I'm able to avoid the SQL injection risks associated with dynamic SQL.