Unanswered: SQL for searching on many fields when only some have values
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...
strSQL = "SELECT * MyTable WHERE (1=1)"
If MyFirstListBox <> "All" Then
strSQL = strSQL & " AND (MyType='" & Request.Form("MyFirstListBox") & "')"
If MySecondListBox <> "All" Then
strSQL = strSQL & " AND (MyDetail='" & Request.Form("MySecondListBox") & "')"