How to create a varchar list?

Question

I need to create a list like this: '00','11','33','44'
When I use ValueList or ListAppend I got '00,11,22,33,44'
My simple codes are as follow:

query name="test" datasource="test">
select empID from Employers
</cfquery>

I tried: <CFSET EmpIDList=ValueList(test.EmpId)>
OR

<cfset MyList="">
<cfloop query="test">
<cfset MyList=#ListAppend(MyList, 'EmpID')>
</cfloop>

Both techniques creates a list that looks like : ' 00,11,22,33,44,55,66' which I can't use this list in the
select * from table where IDList IN ( '00,11,22,33,44,55,66')

Can anyone tell me how can I produce a list with each ID being surrounded by a single quote like ' 00 ', ' 22 ', ' etc so i can use the list on the select statement?

craigkaminsky · Answer

If you use the cfqueryparam tag in your SQL statement, you should be able to denote the sql type of the data and note that it is a list being passed in, thus removing the need to alter how you generate the list of IDs. ColdFusion will (should) take care of the rest.

Example:
<cfquery>
SELECT * FROM table WHERE idlist IN (<cfqueryparam cfsqltype="cf_sql_xxxxx" value="#listvar#" list="true" />)
</cfquery>

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