4

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.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
JGood
  • 522
  • 6
  • 23
  • For what database (including version)? – OMG Ponies May 06 '10 at 22:29
  • It's in SQL Server 2005. Helpful? – JGood May 06 '10 at 23:30
  • YOu might want to read http://www.sommarskog.se/dynamic_sql.html and http://www.sommarskog.se/dyn-search.html – HLGEM Sep 12 '16 at 18:03
  • @HLGEM: Thank you so much. This question is old, but I appreciate you posting that resource and will have to go through it. It is very detailed and I'm excited about it. I remember very well what I ended up doing back then and it was essentially creating an entity framework mashing together strings and such (Don't worry no sql-injection), but I feel sorry for whomever had to work on it after I left. – JGood Sep 12 '16 at 18:20
  • @goodwince, I have referred to these articles so many times through the years. He really is the authority on how to safely do dynamic SQl – HLGEM Sep 12 '16 at 20:58

4 Answers4

1

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: How do I avoid dynamic SQL when using an undetermined number of parameters?

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

Community
  • 1
  • 1
Steve Wortham
  • 21,740
  • 5
  • 68
  • 90
  • Wow.. very detailed post. I was tasked with another item so I'll have to get back to the actual implementation of it. Seems like there isn't a one size fits all for this situation. I need the dynamic capability of multiple table searches based on the possibilities or I'd also go with a SPROC. I parametrize all my queries so there aren't any chances of SQL Injection. Thanks for the heads up. I really enjoyed reading through your post. I will let you know how it turns out. – JGood May 06 '10 at 23:49
0

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.

Neil Barnwell
  • 41,080
  • 29
  • 148
  • 220
  • Your second statement is my current approach. I am currently using outer join on tables that may or may not be necessary and concocting a where class that will search in those tables to see if there is a matching ID. Problem is I need the separate AND/OR/NOT to be able to parse multiple rows of these joined tables and it won't find multiple rows. I was going to switch to using 'IN' but everyone seems to think dynamic sql with IN is bad practice. – JGood May 07 '10 at 13:20
0

Check out SqlBuilder, a utility for Dynamic SQL.

Max Toro
  • 28,282
  • 11
  • 76
  • 114
0

The original post: Write a sql for searching with multiple conditions

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

However, the above query forces table scan. For better performance and more complex scenario (I guess you simplified the question in you original post), consider use dynamic sql. By the way, Linq to SQL can help you build dynamic SQL very easily, like the following:

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);
Community
  • 1
  • 1
Codism
  • 5,928
  • 6
  • 28
  • 29