querying with nulls and lists

Question

I have

QueryExecute(

    SELECT *
    FROM dbo.pages
    WHERE ID IN ( :id )
    ...
   ,
   {
       id : { value = rc.id, cfsqltype : "cf_sql_integer, list : true }
   }

Almost all the time this works. The thing is rc.id can be blank. If it is blank, then all IDs should be matched. I am stuck on how to do this

Answer 1

There are two different options here.

1) You can manipulate the query string to only include the IN statement when the variable is not empty.

How you go about that really really depends on the rest of the query and how you would like to do this.

<cfset sqlstr = "SELECT * FROM dbo.pages" />
<cfif len(rc.id)>
    <cfset sqlstr &= " WHERE ID IN ( :id )" />
</cfif>

You could build up more cleanly with conditional output...

WHERE #len(rc.id) ? "ID IN ( :id )" : ""#

To avoid having to worry about whether WHERE/AND/OR is included one little trick is to include a clause that will always pass (or never in the case of ORing)

WHERE 1=1 
    #len(rc.id) ? "AND ID IN ( :id )" : ""#
    AND ...

2) Pass the parameter twice and perform the check in the database

SELECT *
FROM dbo.pages
WHERE (len(':id') = 0 OR ID IN ( :id ))
...

-- note: this may be problematic if CF is passing null when blank

or just the length

    QueryExecute(

    SELECT *
    FROM dbo.pages
    WHERE (:idlen > 0 OR ID IN ( :id ))
    ...
   ,
   {
       id : { value = rc.id, cfsqltype : "cf_sql_integer, list : true },
       idlen : { value = len(rc.id), cfsqltype : "cf_sql_integer" }
   }

Answer 2

Here I've given sample code about making the queryparam conditional too. For testing purpose I've created some default parameters and values

<cfparam name="id" default="1,2,3,4,5,6">

Set the query details as a string and save it to another variable as @danRoberts mentioned above.

<cfset MyQry = "SELECT * FROM pages ">

Check if the rc.id value is blank. If it isn't, append the IN clause.

<cfif len(rc.id) >
    <cfset MyQry &= " WHERE id IN ( :id ) ">
</cfif>

For reference, I've also added some extra operators.

<cfset MyQry &= " limit 10 "> 

Next, create a new query based on new operator

<cfset qry = new Query( datasource ='yourDataSource' , sql = MyQry) >

Afterward, add a queryParam for the :ID, if the rc.id is not blank

<cfif len(rc.id) >
<cfset qry.addParam( name="id", value="#rc.id#",cfsqltype="cf_sql_varchar",list='true') >
</cfif>

Note : When using the INoperator, you should set list='true' for the queryParam.

Then you execute the query, like below

<cfset Result = qry.execute().getResult() >
<cfdump var="#Result#" />

The WHERE clause with the queryParam will execute only when the rc.ID contains a value.