Dynamic query, where clause help

I do this sort of thing:

<cfset sWhereAnd = "WHERE">

<cfif isdefined("colFilter")>

     #sWhereAnd# col = #colFilter#

    <cfset sWhereAnd = "AND">

</cfif>

<cfif isdefined("someOtherColFilter")>

     #sWhereAnd# someOtherCol = #someOtherColFilter#

    <cfset sWhereAnd = "AND">

</cfif>

[etc]

(that's pseudocode... I'd never use isDefined() or not use a <cfqueryparam> tag for my parameter values).

I don't like doing the somewhat popular WHERE 1=1 approach as it can force a full table scan (all rows in the table will match that, and each WHERE filter expression is applied to every row of the table being filtered), unless the DB optimises it out as noise (which is what it is).  To me it's using bad SQL to cut corners.

To be honest though, I shy away from these generic sort of queries these days.  Most of the genericism never gets used, and more specific requirements are better implemented to meet the precise need.

--

Adam

where 1 = 1

if/else logic goes here.  Use "and" every time.