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" }
   }