Using cfqueryparam With An 'IN' Clause
Posted By : todd sharp Posted At : February 13, 2007 3:29 PM Posted In: SQL, ColdFusion
15
I was just working on a query that was acting up a bit and thanks to the ever brilliant Raymond Camden who bailed me out in about 2 seconds with the solution. I figured I ought to blog the issue for those who may come across this.
Essentially I needed to pass a potential list of values into my query as an 'in' list. My function looked something like this (disclaimer: this is sql server syntax - your db may be different)
<cfargument name="list">
<cfset var inList = listQualify(arguments.list, "'")>
<cfset var myQ = "">
<cfquery name="myQ" datasource="bravo">
select alpha
from charlie
where whiskey in <cfqueryparam value="(#preserveSingleQuotes(inList)#)">
</cfquery>
<cfreturn myQ />
</cffunction>
My first issue was the parenthesis within the value of the cfqueryparam. Ray quickly noted that it should be something like this:
Note the parenthesis outside of the cfqueryparam and the addition of the list="true" attribute and the removal of preserveSingleQuotes. However this still wasn't working for me. The fix was simple however - it seems that the list attribute automatically qualifies the list for you - so the listQualify was not necessary. The end result - which worked perfectly - looked something like this:
<cfargument name="list">
<cfset var myQ = "">
<cfquery name="myQ" datasource="bravo">
select alpha
from charlie
where whiskey in (<cfqueryparam value="#arguments.list#" list="true">)
</cfquery>
<cfreturn myQ />
</cffunction>
"#arguments.list#,,,,0,,,,,0,,,"
will not error out. It ignores the empty commas. This means that you can put things like this:
"#arguments.list#,0"
if you are not sure if the list will have a length. If it does, then zero is usually ignored (when working with IDs and what not). If it does not have a length, then it comes out to be ",0" which CF just treats as "0".
Sweet!
worth adding to the coldfusion cookbook.
http://www.coldfusioncookbook.com/
If arguments.list is an empty list (i.e. arguments.list eq "") then this SQL statement will error out. You should always check anything that's going into an <cfqueryparam list="yes"> tag, to make sure it's not blank.
I just used this provided line with the list of GUIDs without qualifying each one of them, and it worked fine.
Thanks again!
Glad you posted about this!
http://support.microsoft.com/kb/288095
is this the proper syntax?
<cfquery name="myQ" datasource="bravo">
select alpha
from charlie
where whiskey in <cfqueryparam value="(#ListQualify(form.Genus, "'")#)">
answering my own question, the proper form would be this:
where Genus in (<cfqueryparam value="#form.Genus#" list="yes" separator="'">)
Thanks