As software developers, we're always looking for ways to write efficient code. Well, this probably isn't one of those examples, but it's a useful little hack that saves time writing conditional statements for dynamic queries:

The problem

You need to write a query with multiple constraints (WHERE clauses) but not all variables are available all the time and you want to keep your AND statements simple.

The hack

This example uses ColdFusion. Simply write a WHERE clause with a constraint in the first position that will always evaluate as true and won't affect results, like this:

SELECT columns
FROM table
WHERE 1 = 1

And then always add your ANDs with every additional variable, like this:

SELECT columns
FROM table
WHERE 1 = 1
<cfif Len(var1)>AND column1 = var1</cfif>
<cfif Len(var1)>AND column2 = var2</cfif>
<cfif Len(var3)>AND column3 = var3</cfif>
...etc

Of course, you need to make sure the CFIF uses the right function for your purpose (i.e. maybe IsDefined() or CompareNoCase() are better functions) and you quote the variables for text column types, if needed.

Deride it all you want, but it's easy and effective if used in the right context.