|
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.
|
