ColdFusion Dynamic SQL with cfif

Question

I'm trying to write a query which would accept 1 of 4 possible parameters passed from another page. The user would select one of these parameters to search on and then the query would evaluate which of those was selected based on the passed parameter. I'm trying to use a cfif to accomplish this but I must be missing something. Maybe a cfelse, and OR, or maybe I'm way off. Would this be better as cffunction? I get an error "variable DivisionCode" is undefined. Thank you.

<cfquery name="Summary" datasource="dsn">
    SELECT
        *
    FROM
        disposal
    WHERE 1=1

    <cfif DivisionCode NEQ "false">
    AND DivisionCode = <cfqueryparam value="#url.DivisionCode#" 
   cfsqltype="cf_sql_varchar">
   </cfif>
   <cfif DistrictCode NEQ "false">
    AND DistrictCode = <cfqueryparam value="#url.DistrictCode#" 
   cfsqltype="cf_sql_varchar">
   </cfif>
   <cfif SiteCode NEQ "false">
    AND SiteCode = <cfqueryparam value="#url.SiteCode#" 
   cfsqltype="cf_sql_varchar">
  </cfif>
  <cfif RegionCode NEQ "false">
    AND RegionCode = <cfqueryparam value="#url.RegionCode#" 
  cfsqltype="cf_sql_varchar">
  </cfif>
  ORDER By
    CYear DESC
  </cfquery>

Answer 1

I would look at variable scoping and checking to make sure the variable exists. So, let's just look at one of your conditionals.

<cfif StructKeyExists(url,"DivisionCode") AND url.DivisionCode NEQ "false">
   AND DivisionCode = <cfqueryparam value="#url.DivisionCode#" cfsqltype="cf_sql_varchar">
 </cfif>

Adding the "url." prefix before your "DivisionCode" will specify the scope in which you are looking for your variable. This won't really help to solve your problem directly, but it will make your code a bit clearer and error messages a bit more helpful.

Adding the StructKeyExists() checks to make sure that variable is available in the URL in the first place. based on how you wrote your code, I'm guessing you don't want to filter by a URL variable that wasn't included, so that will take care of that.

Just do the same thing on each cfif and you should be right as rain.

Answer 2

Two options come to my mind: You can either make it work like an include or call it as a function.

Isolated Include (less common)

your_query.cfm

<!--- declaring possible incoming parameters --->
<cfparam name="attributes.datasource"   type="string" default="dsn">
<cfparam name="attributes.divisionCode" type="string" default="">
<cfparam name="attributes.districtCode" type="string" default="">
<cfparam name="attributes.siteCode"     type="string" default="">
<cfparam name="attributes.regionCode"   type="string" default="">

<!--- declaring outgoing variable --->
<cfparam name="attributes.variable"     type="string" default="your_query">

<!--- retrieve result --->
<cfquery name="Summary" datasource="#attributes.datasource#">
    SELECT
        *
    FROM
        disposal
    WHERE 1=1

    <cfif len(attributes.divisionCode) gt 0>
        AND DivisionCode = <cfqueryparam value="#attributes.divisionCode#" cfsqltype="cf_sql_varchar">
    </cfif>
    <cfif len(attributes.districtCode) gt 0>
        AND DistrictCode = <cfqueryparam value="#attributes.districtCode#" cfsqltype="cf_sql_varchar">
    </cfif>
    <cfif len(attributes.siteCode) gt 0>
        AND SiteCode = <cfqueryparam value="#attributes.siteCode#" cfsqltype="cf_sql_varchar">
    </cfif>
    <cfif len(attributes.regionCode) gt 0>
        AND RegionCode = <cfqueryparam value="#attributes.regionCode#" cfsqltype="cf_sql_varchar">
    </cfif>

    ORDER BY
        CYear DESC
</cfquery>

<!--- set outgoing variable --->
<cfset caller[attributes.variable] = Summary>

invoking.cfm

<cfmodule divisionCode="foo" regionCode="bar" variable="my_query" template="your_query.cfm">
<cfdump var="#my_query#">

<!--- or --->

<cfset params = {}>
<cfset params.siteCode = "bla">
<cfif structKeyExists(url, "regionCode")>
    <cfset params.regionCode = url.regionCode>
</cfif>

<cfmodule attributeCollection="#params#" template="your_query.cfm">
<cfdump var="#your_query#">

<!--- etc. --->

The <cfmodule> tag isolates its content, meaning that no variables (except the ones assigned to the caller scope) within the your_query.cfm are visible to the calling template (invoking.cfm). This approach is basically what native CF tags like <cfhttp> and <cffile> do.

---

Function (more common)

<cffunction name="yourQuery" access="public" output="false" returnType="query">

    <!--- declaring possible incoming parameters --->
    <cfargument name="datasource"   type="string" default="dsn">
    <cfargument name="divisionCode" type="string" default="">
    <cfargument name="districtCode" type="string" default="">
    <cfargument name="siteCode"     type="string" default="">
    <cfargument name="regionCode"   type="string" default="">

    <!--- retrieve result (the local. scope makes the variable exist in this function only) --->
    <cfquery name="local.Summary" datasource="#arguments.datasource#">
        SELECT
            *
        FROM
            disposal
        WHERE 1=1

        <cfif len(arguments.divisionCode) gt 0>
            AND DivisionCode = <cfqueryparam value="#arguments.divisionCode#" cfsqltype="cf_sql_varchar">
        </cfif>
        <cfif len(arguments.districtCode) gt 0>
            AND DistrictCode = <cfqueryparam value="#arguments.districtCode#" cfsqltype="cf_sql_varchar">
        </cfif>
        <cfif len(arguments.siteCode) gt 0>
            AND SiteCode = <cfqueryparam value="#arguments.siteCode#" cfsqltype="cf_sql_varchar">
        </cfif>
        <cfif len(arguments.regionCode) gt 0>
            AND RegionCode = <cfqueryparam value="#arguments.regionCode#" cfsqltype="cf_sql_varchar">
        </cfif>

        ORDER BY
            CYear DESC
    </cfquery>

    <!--- return result --->
    <cfreturn local.Summary>
</cffunction>

invoking.cfm

<cfset my_query = yourQuery("dsn", "foo", "", "bar")>
<cfdump var="#my_query#">

<!--- or --->

<cfset your_query = yourQuery(
    datasource: "dsn",
    siteCode: "bla"
    regionCode: ( structKeyExists(url, "regionCode") ? url.regionCode : "" )
)>
<cfdump var="#your_query#">

<!--- etc. --->

That should give you an idea about what you can achieve.