CODEWITHSUNDEEP
asp.netsqlvb.netsearch
Tommy Dubé-Leblanc
Tommy Dubé-Leblanc

Reputation: 317

Building a valid SQL Query with optional parameters for search function

I have a search function de build. We are using pure ASP.NET w\ VB.NET

We have multiple DropDownLists and we're building a search query with whatever was selected in those DDLs. My question is, how can I handle the blank values (unselected dropdownlist values) with the SQL Query ? I'm using AND operators in the query so if anything is blank it'll fail the search. If the dropdownlist has no selected value, i don't want the value to be part of the search. It would be easy to code with just 2-3 parameters, but we're looking thru at least 10 items and doing a SWITCH CASE or multiple IFs would soon become mayhem.

I'm sure there's an easier solution out there.

Thanks for the help Have a nice day folks.

Upvotes: 0

Views: 1822

Answers (1)

King
King

Reputation: 339

I guess you could default the parameters to NULL in the sproc and do something like

...
Where 
(someField1 = @Param1 OR @Param1 IS NULL) AND
(someField2 = @Param2 OR @Param2 IS NULL) AND
(someField3 = @Param3 OR @Param3 IS NULL) ....

etc

That way, if you pass NULL, that particular check will be true.

Upvotes: 3

Related Questions