Dynamic SQL Query Question

I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.

I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @.tblNode AND tblCorp = @.tblCorp AND tblService = @.tblService

The @.tblNode, @.tblCorp, @.tblService are all pulling from 3 separate drop down controls.

Ok easy enough.

However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.

Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?

I guess I am looking to see if a wild card can be used so tblNode = % or something.

The server is MSSQL

Thank you

You can use either of the 2 approaches mentioned in this article and its comments:http://www.emxsoftware.com/Optional+Parameters+in+SQL+Server+Search+Queries

|||

Thank you for your help, while this is what I need... would you happen to know how to set the value of the drop downs I am using = NULL?

I can pull now based on the criteria of the drop downs, but the default values should pull everything. That is really where I am stuck right now.

|||

You leave your Select All listItem in your DropDownList to empty like:

<asp:ListItemValue="">ALL</asp:ListItem>

Your SQl where clause:

Where tblNode =ISNULL(@.tblNode,tblNode) AND tblCorp=ISNULL(@.tblCorp, tblCorp) AND tblService= ISNULL(@.tblService, tblService)

and:

If you use a SqlDataSource, you need to set this property too:CancelSelectOnNullParameter="false"

|||

Thank you both, that actually did it and I am up and working.

I appreciate you both taking the time to explain it for me.