Using quoted list with in()

[athanasiusrc]

I can't get this query to return results unless I manually put in a list of zip codes ('80918','80917','80920','80902','80922','80907','80909','80919')

When I have the same list fed in as a variable SQL adds extra quotes to the list and I get no results. What do I need to do to prevent this?

declare
@ZipCodes varchar(300)


SELECT
StoreName,
Address,
City,
StateAbbreviation,
PostalCode,
PhoneNumber,
WebAddress
FROM
CatholicStores
JOIN
States
ON
State_Pk = State_Fk
WHERE
PostalCode IN (@zipcodes)
ORDER BY
PostalCode,
StoreName

[Dan_Bracuk]

Two things to try are ListQualify() or cfqueryparam list="yes"

[cf_dev2]

athanasiusrc,

If you have a non-quoted list of values, try using cfqueryparam. Its untested but should be something like this

<cfset theList ="80918,80917,80920,80902,80922,80907,80909,80919">
<cfif listLen(theList) eq 0>
<cfset theList = listAppend(theList, 0)>
</cfif>

<cfquery ...>
SELECT
StoreName,
Address,
City,
StateAbbreviation,
PostalCode,
PhoneNumber,
WebAddress
FROM
CatholicStores
JOIN
States
ON
State_Pk = State_Fk
WHERE
PostalCode IN
(
<cfqueryparam value="#theList#" cfsqltype="cf_sql_varchar" list="true">
)
ORDER BY
PostalCode,
StoreName
</cfquery>

[athanasiusrc]

Using list yes in the query param and switching between quoted and nonquoted don't fix the problem.

[cf_dev2]

Are you using dynamic sql? If not post the exact query you tried.

[athanasiusrc]

I have also tried running it as a stored procedure.


<cfquery name="getlocations" Datasource="#application.ds#" >
SELECT
StoreName,
Address,
City,
StateAbbreviation,
PostalCode,
PhoneNumber,
WebAddress
FROM
CatholicStores
JOIN
States
ON
State_Pk = State_Fk
WHERE
PostalCode IN (<cfqueryparam value="#'preservesinglequotes(attributes.zipcodelist)'#" cfsqltype="CF_SQL_VARCHAR" list="Yes">)
ORDER BY
PostalCode,
StoreName
</cfquery>

[cf_dev2 · Accepted solution]

cfqueryparam handles quoting for you. remove all single quotes in: #attributes.zipcodelist# and use

<cfqueryparam value="#attributes.zipcodelist#" cfsqltype="CF_SQL_VARCHAR" list="Yes">

[athanasiusrc]

That did it!