Problem solve Get help with specific problems with your technologies, process and projects.

Performing a dynamic search with the "any" option in SQL

Read part two of this answer here: Constructing a dynamic search SQL statement

I am doing a multiple field database search, and my problem lies in the options that a user has. In each field, the user can specify a specific value or any value. I take each value from the form and store them as variables. I then have an SQL statement constructed which takes these variables as conditions and returns matching records. If they choose a specific value for each field, there is no problem but if they choose "any", I want to be able to use the same SQL statement as well. How do I manage this?

 strSQL = "SELECT tblReg.Username, tblBasic.DOB," &_ " tblPhys.*" &_ " FROM tblBasic INNER JOIN" &_ " (tblReg INNER JOIN " &_ " tblPhys ON tblReg.ID =" &_ " tblPhys.ID) ON tblBasic.ID = tblReg.ID " &_ " WHERE ((tblBasic.Sex = '" & strSexValue & "') AND " &_ " (tblBasic.Smoke = "& strSmokeValue & " ) AND " &_ " (tblPhys.HairColour = '" & strHairValue & "') AND " &_ " (tblPhys.EyeColour = '" & strEyeValue & "') AND " &_ " (tblPhys.Build = '" & strBuildValue & "') AND " &_ " (tblBasic.County = '" & strCountyValue & "') AND " &_ " (tblBasic.Rel = '" & strRelValue & "'));"

Constructing dynamic SQL is a common requirement when programming a database interface. For example, a database-driven Web site might allow a user to select from dropdown select menu options, check checkboxes and radio buttons, or even enter search terms in a form field. These user actions, and sometimes non-actions, must be interpreted by the database interface, and then used to construct the appropriate SQL. This is where you are: constructing the strSQL string from concatenated literals and variable values.

The specific language you're using is unfamiliar to me (although I would guess ASP or VB). Let me therefore offer suggestions using pseudocode.

Let's start with your original query, but use dummy names to make the structure stand out. We'll write the SQL string in black text, with programming language control statements and variables in italic red:

 select a, b, c, d, e from sometables where a = a and b = b and c = c

Now let's consider the "any" option for a. If it's a dropdown select menu, it may have an option specifically labelled ANY, or perhaps ALL OPTIONS. Often this option will be pre-selected, which means that unless the user picks a different option, the intent is clearly to return search results without a restriction on the a values. If it's a checkbox, it might be labelled "Select active records only," which means that if it's not checked, the intent is clearly to return all records, not just active ones, at least insofar as the a column is concerned.

From the SQL perspective, the "any" option requires not including a search condition for that column. It would be wrong to use where a = '' (an empty string), since that would search for an empty value, not any values. Nor can we use where a = 'ANY', since that's not a valid value. Neither will where a is null work, since that again selects only certain rows (if any, since there might be no nulls in that column). The most disagreeable solution, which I've actually seen someone use in practice, would be where a in (select a from atable), which is not only inefficient but totally unnecessary.

So we need a test for the "any" option. How we detect the "any" option will vary. For example, the pre-selected dropdown option value could be 0, a convenient value since many lookup tables use an autonumber key starting at 1. If it's a checkbox, and the user doesn't check it, then the form will be submitted without the checkbox form field, so the interface needs to test to see whether that field was submitted (if it was, the value will be "checked").

In Part 2 of this answer, we will see how to write tests for the "any" option, and how to use these tests to generate the dynamic SQL statement.

Dig Deeper on Oracle and SQL