Forming a SQL IN expression

Question

If I use a hard-coded set of string values in an IN
expression, the query returns the expected data.

If I then replace the hard-coded expression with an
equivalent CF variable embedded in a cfqueryparam tag, the query
executes but returns no records. A cfdump of the CF variable
indicates it is exactly the correct pair of single-quoted values
that were previously hard-coded.

A cfdump of the query result set shows the IN expression is a
single row array containing 'a','b'

Any ideas what might be wrong?

Jim

Jim_Gibson_sbswa_com · Accepted Answer

Jim Gibson sbswa.comI tried directly assigning the CF variable just before thequery, to make sure there are no surprises:When using cfqueryparam you should not need to single quotevarchar values. I think the single quotes are skewing your results.The resulting query is probably looking for a category thatliterally equals 'a' or 'b' (including the single quotes). Remove the single quotes and the query should work asexpected.That took care of it. Thank you very much! Using thecfqueryparam "list" attribute, and using a list of unquoted columnvalues solved the problem. I also noted that the unquoted listneeds to contain no spaces, just comma separators. I am now gettingthe expected records back.

-__cfSearching__- · Answer

You need to use cfqueryparam's "list" attribute to indicate the parameter contains multiple values. When you do not use list="true", then 'a,b' is treated as a single value. The resulting query is equivalent to saying:

SELECT * FROM email WHERE category = 'a,b'

Since your table probably does not contain any records where the category equals "a,b", the query returns zero records.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded