I'm trying to build a "search screen" where a user can enter a lot of detailed information or just a couple items. They will be searching on a database of people, so there are about 20-30 different fields they might fill out.

My question is, what is the most efficient way to query the database when I don't know in advance which fields will have values and which will be left blank? If a field is left blank, I want to match ALL values in that column.

I assume I could, using string manipulation in the code, build an SQL query built from a series of concatenated AND clauses, but ideally I'd like this to be in an Oracle stored procedure to which I will always pass all the values the user enters on the screen.

I'm sure this problem has been solved (or at least approached) a million times before, so I'd like to learn from those who have ventured ahead of me...

Thanks,

DT

i wrote an article on how to approach this problem:

The "any" option in dynamic search SQL
(registration may be required, but it's free)

And for an example using the concatenated AND's:

strSQL = "SELECT * MyTable WHERE (1=1)"
If MyFirstListBox <> "All" Then
strSQL = strSQL & " AND (MyType='" & Request.Form("MyFirstListBox") & "')"
End If
If MySecondListBox <> "All" Then
strSQL = strSQL & " AND (MyDetail='" & Request.Form("MySecondListBox") & "')"
End If