Building a valid SQL Query with optional parameters for search function

Question

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.

Are you building the SQL WHERE clause dynamically in a string in VB.NET or are you passing parameters to a stored procedure? — wweicker
– wweicker, Commented Oct 20, 2011 at 20:45

King · Accepted Answer · 2011-10-20 20:38:55Z

3

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.

answered Oct 20, 2011 at 20:38

King

3391 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building a valid SQL Query with optional parameters for search function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related