CODEWITHSUNDEEP
c#sql-server-2008dataset
Saleh
Saleh

Reputation: 2719

How to Do a Query with Optional Search Parameters?

I have these columns in my table;

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

and I am using C#

  1. 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.

  2. 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

  3. or

    WHERE WorkPlace = @WorkPlace AND
WorkPlace1 = @WorkPlace1

  4. 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.

Upvotes: 1

Views: 499

Answers (2)

Ryk
Ryk

Reputation: 3102

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

Upvotes: 0

VinayC
VinayC

Reputation: 49215

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!

Upvotes: 1

Related Questions