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

Constructing a dynamic search SQL statement

In part one of this answer, "Performing a dynamic search with the "any" option in SQL", we examined the need to test for the "any" option in fields used in a database search request. In this part, we will use these tests to construct the dynamic SQL statement.

For any type of field driving the database search request, whether dropdown select menu, checkbox, etc., we need to have a programming language test. The exact type of test will vary with the nature of the field, and whether it was even submitted. Let's generalize the test into pseudocode like this:

 if field is not "any" some action endif

We need to use a test for the "any" option on all columns that allow it. The test will ensure that a condition is generated for that column for every option value except the "any" option.

 select a, b, c, d, e from sometables where if a is not "any" a = a and endif if b is not "any" b = b and endif if c is not "any" c = c endif

Any good programmer will immediately see a problem here. If a is not the "any" option, we generate a = a and into the SQL. But what if the other two options are "any" and therefore not included? That trailing and will cause a runtime syntax error!

Can we put the and in front of the condition rather than after? That makes things somewhat easier, but there's still a problem with the first one, which needs WHERE instead of AND. How do we know which one will be the first? Or even if there will be one? We can't say WHERE ... and then not generate any conditions!

One approach is to use a "switch" which is initialized to the string 'WHERE'. As soon as the first condition is generated, another control statement within the IF block resets the switch to 'AND'. This has to be done inside every IF block, since we don't know which one will be first. This method works, but it's messy.

A better solution is the simple "WHERE 1=1" technique:

 select a, b, c, d, e from sometables where 1 = 1 if a is not "any" and a = a endif if b is not "any" and b = b endif if c is not "any" and c = c endif

WHERE 1=1 will always be true, and now we can optionally append additional conditions after it, using AND, without any further tests or code to handle the first one. If all the options are the "any" option, then no conditions are generated, but that's fine, since WHERE 1=1 by itself will return all rows in the table.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.