Dynamic query, where clause help

[matthisco]

Hi Folks,

Using my code below to generate a query.

When using more than one condition, I'm not sure how to work out where the AND goes.

Can anyone please help?

Thankyou

WHERE
<cfif stafffilter neq "">
deviceofficer = #stafffilter#
</cfif>
<cfif assetid neq "">
AND deviceasset = '#assetid#'
</cfif>
<cfif isdefined("noasset")>
AND deviceasset = ''
</cfif>
  <cfif  isdefined("noserial")>
AND deviceserial = ''
</cfif>
<cfif serial neq "">
AND deviceserial = '#serial#'
</cfif>
<cfif servicearea neq "">
AND deviceservice = #servicearea#
</cfif>
ORDER by locationname

[Dan_Bracuk]

where 1 = 1

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

[Adam Cameron.]

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