Handling Optional Query Parameters

Question

I have a GridView bound to an DataSource query with parameters tied to form controls.

What is the standard / best-practices way to handle optional query parameters?

Answer 1

Something like.. set the control parameter to -1 then in your query do something like...

Select * From Blah Where
(Somefield = @param or @param = -1)

Answer 2

On your DataSource, have your ControlParam use the DefaultValue:

<asp:ControlParameter Name="CustomerID" 
    ControlID="DropDownList1" PropertyName="SelectedValue"  
    DefaultValue="-1" />

Then in your SQL statement, check for that same default value from your ControlParameter:

 SELECT * 
 FROM Invoices
 WHERE (CustomerID = @CustomerID OR @CustomerID = -1)

Answer 3

How would this work with Multiple Optional Parameters?