Yesterday I was working on a new feature for the Scribblar.com API that would allow users to delete multiple rooms at once by passing in a series of roomids (in my case those roomids are varchars, an example roomid would be 'g8m2ps88').
In order to process these easily in one SQL statement I wanted to use the SQL IN statement.

view plain
1SELECT roomid, id from rooms
2WHERE roomid IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#roomidlist#" list="true" separator="," /> )
This worked fine if the roomids were defined without spaces in between. The following list worked fine:
view plain
1<cfset roomids="g8m2ps88,u3s29kk">
The following worked without errors, but returned the wrong number of results:
view plain
1<cfset roomids="g8m2ps88, u3s29kk">
Spaces between the list elements would result in only one result from the SELECT IN query and I knew that that was wrong, I should see two results. It appeared that the list attribute in cfqueryparam would ignore any items after the space, even though dumping a Listlen(roomids,",") would correctly show all three items.

I had several options for fixing this, and since my roomids never contain any spaces themselves I simply removed any spaces that may exist. This is what I am using now:

view plain
1SELECT roomid, id from rooms
2WHERE roomid IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#Replace(roomids, ' ', '', 'all')#" list="true" separator="," /> )
I double checked with Ray Camden as I wasn't sure if I had overlooked another obvious solution (thanks for your advice Ray!), but Mr Jedi himself approved of my workaround - the only caveat being that this will not work if your roomids contain spaces themselves (such as '3kd 933js').

Hope this helps someone, it certainly took me a little while to figure out what was going on.