While inserting null for values while using cfqueryparam has been written about before, I'd like address the specific case of lists. When I say lists, I'm referring to (in MySql) queries that use something along the lines of
SELECT this, that FROM table WHERE id IN (12, 13, 29, 56)
So the paramerterized version would look like this:
<cfset id_list = "12,13,29,56">
<cfquery name="aQuery" datasource="dsn">
Select this, that
From table
Where id in
(
<cfqueryparam value="#id_list#" >
)
</cfquery>
That looks fine and dandy, unless your list (id_list in this case) is null or empty. Coldfusion 6 doesn't insert a null for you automatically, which causes MySql to throw an error. To remedy this, I employed a solution similar to the null-ifying of the parameter. The addition being that the list parameter also needs to be set inversely to the null parameter. So if the list parameter is hard coded to true, null will not be inserted.
<cfset id_list = "">
<cfquery name="aQuery" datasource="dsn">
Select this, that
From table
Where id in
(
<cfqueryparam value="#id_list#"
list="#LEN(id_list) GT 0#"
null="#LEN(id_list) EQ 0#"
>
)
</cfquery>
Note: It looks like CF8 works fine without this method.
Links:
cfqueryparam and conditional handling of NULL’s
