When writing SQL in Coldfusion where the ‘IN’ operator is used you will have to use the list attribute of the cfqueryparam tag, and if using a delimiter other than a comma will need to include the ‘separator’ attribute to indicate the delimiting character.  This, of course, only applies if the list of available values are stored externally in a variable.  Let’s take a look at how to use the ‘IN’ operator in Coldfusion and some varying scenarios that either will or will not work.

First: Using actual values (works)

<cfset variables.dsn = "cfartgallery" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN ('Morph','Things','Ideas')
</cfquery>

So this is a query that could be Coldfusion independent in the sense that this could be copied into your SQL developer of choice and run successfully. Now let’s move the “‘Morph’,'Things’,'Ideas’” into a variable and execute it using a cfqueryparam tag with a simple value.

Second: Using cfqueryparam as a single value (inaccurate)

<cfset variables.dsn = "cfartgallery" />

<cfset variables.nameList = "Morph,Things,Ideas" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN (<cfqueryparam value="#variables.nameList#" cfsqltype="cf_sql_varchar" />)
</cfquery>

I listed this query as inaccurate because it is not syntactically invalid. This will return an empty query set since no ‘artName’ column record has a value equal to ‘Morph,Things,Ideas’. If we were to run an insert statement and insert this string and rerun the above query it would return that record. The thing to remember is that using cfqueryparam as above will always look at a single value which is in the ‘value’ attribute. Let’s look at an example that maybe illustrates this a little more clearly.

Third: Using individual values as cfqueryparam (works, but inefficient)

<cfset variables.dsn = "cfartgallery" />

<cfset variables.value1 = "Morph" />
<cfset variables.value2 = "Things" />
<cfset variables.value3 = "Ideas" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN (<cfqueryparam value="#variables.value1#" cfsqltype="cf_sql_varchar" />,<cfqueryparam value="#variables.value2#" cfsqltype="cf_sql_varchar" />,<cfqueryparam value="#variables.value3#" cfsqltype="cf_sql_varchar" />)
</cfquery>

So the above will return the same query recordset as the first query ran, and we’ve protected our dynamic values with cfqueryparam. However, this is a lot of code. I have only three values, in a real-world scenario this could be a ridiculous amount of data that could require constant updating. So this brings me to using cfqueryparam and indicating that it is a list of values instead of a single value using the ‘list’ attribute. Next is an example using it.

Fourth: Using cfqueryparam as a list of values (works)

<cfset variables.dsn = "cfartgallery" />

<cfset variables.nameList = "Morph,Things,Ideas" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN (<cfqueryparam value="#variables.nameList#" list="true" cfsqltype="cf_sql_varchar" />)
</cfquery>

This select statement will return the same recordset as the previous query, but without the maintenance nightmare the one above presents. Now our list can grow infinitely (minus any actual database restrictions on how many values the ‘IN’ operator can evaluate). Also, assuming that the query is encapsulated, the list should be able to grow without ever needing to touch the query or function that contains it reducing deployment risks.

Lastly, what if the list was pipe-delimited? Well, Coldfusion has you covered. When using the list attribute you then have an optional attribute ‘separator’ where you can specify a specific delimiter.

Fifth: Using a special delimiter (works)

<cfset variables.dsn = "cfartgallery" />

<cfset variables.nameList = "Morph|Things|Ideas" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN (<cfqueryparam value="#variables.nameList#" list="true" separator="|" cfsqltype="cf_sql_varchar" />)
</cfquery>

Same query as before, but now the list is separated by pipes (|) and a ‘separator’ attribute is added to indicate that a delimiter other than the default comma is being used. It is important to remember this in the case that you do have a special delimiter. The reason is because you will not get an error. Instead you will get an empty recordset because Coldfusion will treat the pipe-delimited list as a single value as opposed to a list of values.