How to use integer value list in SQL IIF in "IN" clause in coldfusion parameterized query?

Question

How can I use IIF in an "IN" clause in this way in a paramaterized coldfusion query ?

<cfset id_check = '95,1' /> 

WHERE 
  id IN (
    IIF( 
      len( <cfqueryparam value="#id_check#" list="yes" /> ) > 0,
      <cfqueryparam cfsqltype="cf_sql_integer" value="#id_check#" list="yes" />,
      id 
    ) 
  )

Answer 1

I think you have the <cfqueryparam> functionality backwards. The answer depends on what the id is inside the IN (). If that is supposed to be a ColdFusion variable, then the following will work for you.

<cfif listLen(cash_drawer_number)>
  <cfset idList = cash_drawer_number>
<cfelse>
  <cfset idList = id>
</cfif>
<cfquery name="foo" datasource="datasource">
  SELECT id
  FROM test
  WHERE 
    id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#idList#" list="yes" />)
</cfquery>

If that is the same things as id column in the query then it would mean that you are trying to get all the records.

<cfquery name="foo" datasource="datasource">
  SELECT id
  FROM test
  WHERE 
    1=1
    <cfif listLen(cash_drawer_number)>
      AND id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#cash_drawer_number#" list="yes" /> )
    </cfif>
</cfquery>