Skip to main content
A
athanasiusrc
Inspiring
June 13, 2007
Answered

Using quoted list with in()

  • June 13, 2007
  • 5 replies
  • 897 views
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
This topic has been closed for replies.
Correct answer cf_dev2
cfqueryparam handles quoting for you. remove all single quotes in: #attributes.zipcodelist# and use

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

5 replies

A
athanasiusrcAuthor
Inspiring
June 13, 2007
That did it!
A
athanasiusrcAuthor
Inspiring
June 13, 2007
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>
C
cf_dev2Correct answer
Inspiring
June 13, 2007
cfqueryparam handles quoting for you. remove all single quotes in: #attributes.zipcodelist# and use

<cfqueryparam value="#attributes.zipcodelist#" cfsqltype="CF_SQL_VARCHAR" list="Yes">
A
athanasiusrcAuthor
Inspiring
June 13, 2007
Using list yes in the query param and switching between quoted and nonquoted don't fix the problem.
C
cf_dev2
Inspiring
June 13, 2007
Are you using dynamic sql? If not post the exact query you tried.
C
cf_dev2
Inspiring
June 13, 2007
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>
D
Dan_Bracuk
Inspiring
June 13, 2007
Two things to try are ListQualify() or cfqueryparam list="yes"
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices