How to Do a Query with Optional Search Parameters?

Question

I have these columns in my table;

Categ - int
WorkPlace - int
WorkPlace1 - int
AsignedAs - int

and I am using C#

I want to select all rows with OR if just one of the search parameters is not equal to 0 I added a default value to know if the user is selecting from combobox or not.
I want to select with AND with this sequence : if more than search parameters is not equal to 0 (user select value) some thing like this
```
WHERE Categ = @categ AND WorkPlace =
@WorkPlace (user select two values
```
and the others are blank or equal to 0

or

WHERE WorkPlace = @WorkPlace AND
WorkPlace1 = @WorkPlace1

or

WHERE Categ = @Categ AND WorkPlace = @WorkPlace1

(user select these values and are not blank so we add and to the query)

and so on....

So I want check for values if are empty or not to build the right query.

Duplicate? http://stackoverflow.com/questions/333965/sql-search-query-for-multiple-optional-parameters — Goran, Feb 16 '11 at 00:53
can you please re-word your question to better explain the logic? As it stands right now, I find it really difficult to understand what logic you're using. Also does #3 in your question make sense or is it a typo. Also what is not clear is, is the difficulty just trying to figure out what's select and what's not? Or is the difficulty with SQL? If it's the former can you please provide some code you're using currently and tell us what is going wrong? — Shiv Kumar, Feb 16 '11 at 04:14
I agree with @Shiv, this is pretty hard to understand, although I can see you've tried. Maybe if you wrote some pseudocode and gave some examples? — Justin Morgan - On strike, Feb 16 '11 at 05:01

score 1 · Accepted Answer · answered Feb 16 '11 at 04:13

See the link pointed out by Goran to handle optional values - in your case, you need to pass NULL to stored proc if the option is not selected by user. IF you wish to continue to use zero (or some other value) to indicate non-applicability of the parameter than you can modify the query such as

WHERE 
   (COALESCE(@Categ, 0) = 0 OR Categ = @Categ) AND 
   (COALESCE(@WorkPlace, 0) = 0 OR WorkPlace = @WorkPlace) AND 
   (COALESCE(@WorkPlace1, 0) = 0 OR WorkPlace1 = @WorkPlace1) AND 
   (COALESCE(@AsignedAs, 0) = 0 OR AsignedAs= @AsignedAs)

Hope you get the general idea!

score 0 · Answer 2 · answered Feb 16 '11 at 05:10

You can also use the CASE approach. I find this very useful and easy, and the SQL Query Optimizer seems to select better execution plans for this type of where lcause:

WHERE CASE WHEN @Categ = 0 THEN 0 ELSE Categ END = CASE WHEN @Categ = 0 THEN 0 ELSE @Categ END
AND CASE WHEN @WorkPlace = 0 THEN 0 ELSE WorkPlace END = CASE WHEN @WorkPlace = 0 THEN 0 ELSE @WorkPlace END
AND CASE WHEN @WorkPlace1 = 0 THEN 0 ELSE WorkPlace1 END = CASE WHEN @WorkPlace1 = 0 THEN 0 ELSE @WorkPlace1 END
AND CASE WHEN @AsignedAs = 0 THEN 0 ELSE AsignedAs END = CASE WHEN @AsignedAs = 0 THEN 0 ELSE @AsignedAs END

How to Do a Query with Optional Search Parameters?

2 Answers2