Building Query from Multi-Selection Criteria

I am开发者_运维技巧 wondering how others would handle a scenario like such:

Say I have multiple choices for a user to choose from.

Like, Color, Size, Make, Model, etc.

What is the best solution or practice for handling the build of your query for this scneario?

so if they select 6 of the 8 possible colors, 4 of the possible 7 makes, and 8 of the 12 possible brands?

You could do dynamic OR statements or dynamic IN Statements, but I am trying to figure out if there is a better solution for handling this "WHERE" criteria type logic?

EDIT: I am getting some really good feedback (thanks everyone)...one other thing to note is that some of the selections could even be like (40 of the selections out of the possible 46) so kind of large. Thanks again!

Thanks,

S

What I would suggest doing is creating a function that takes in a delimited list of makeIds, colorIds, etc. This is probably going to be an int (or whatever your key is). And splits them into a table for you.

Your SP will take in a list of makes, colors, etc as you've said above.

YourSP '1,4,7,11', '1,6,7', '6'....

Inside your SP you'll call your splitting function, which will return a table-

SELECT * FROM
Cars C
JOIN YourFunction(@models) YF ON YF.Id = C.ModelId
JOIN YourFunction(@colors) YF2 ON YF2.Id = C.ColorId

Then, if they select nothing they get nothing. If they select everything, they'll get everything.

What is the best solution or practice for handling the build of your query for this scenario?

Dynamic SQL.

A single parameter represents two states - NULL/non-existent, or having a value. Two more means squaring the number of parameters to get the number of total possibilities: 2 yields 4, 3 yields 9, etc. A single, non-dynamic query can contain all the possibilities but will perform horribly between the use of:

1. ORs
2. overall non-sargability
3. and inability to reuse the query plan

...when compared to a dynamic SQL query that constructs the query out of only the absolutely necessary parts.

The query plan is cached in SQL Server 2005+, if you use the sp_executesql command - it is not if you only use EXEC.

I highly recommend reading The Curse and Blessing of Dynamic SQL.

For something this complex, you may want a session table that you update when the user selects their criteria. Then you can join the session table to your items table.

This solution may not scale well to thousands of users, so be careful.

If you want to create dynamic SQL it won't matter if you use the OR approach or the IN approach. SQL Server will process the statements the same way (maybe with little variation in some situations.)

You may also consider using temp tables for this scenario. You can insert the selections for each criteria into temp tables (e.g., #tmpColor, #tmpSize, #tmpMake, etc.). Then you can create a non-dynamic SELECT statement. Something like the following may work:

SELECT <column list>
FROM MyTable
WHERE MyTable.ColorID in (SELECT ColorID FROM #tmpColor)
    OR MyTable.SizeID in (SELECT SizeID FROM #tmpSize)
    OR MyTable.MakeID in (SELECT MakeID FROM #tmpMake)

The dynamic OR/IN and the temp table solutions work fine if each condition is independent of the other conditions. In other words, if you need to select rows where ((Color is Red and Size is Medium) or (Color is Green and Size is Large)) you'll need to try other solutions.