Feeding a list into a query

[MCL97]

Hi,

I have a simple table that contains two columns (StaffID, Code). Both take integer values. I now have a list of StaffIDs that I want to pull up. How can I feed this list into a query. When I tried the following, I got an error because the list is a string while the query is expecting integer values.

quote:

---

<Cfset idList = "1,5,6,10">
<cfquery name="test" datasource="myDB">
select Code
from StaffTable
where StaffID in (#idList#)
</cfquery>

---

How to correct the above? I know I can do the following, but it's a bit unwieldy.

quote:

---

<Cfset idList = "1,5,6,10">
<cfquery name="test" datasource="myDB">
select Code
from StaffTable
where StaffID = listGetAt(idList,1)
<cfloop from="2" to="#ListLen(idList)#" index="idx">
or StaffID = #listGetAt(idList,idx)#
</cfloop>
</cfquery>

---

Thanks.
Min

[The_ScareCrow]

The first query posted should have worked.

What was the error ?
Turn on debugging and see what the query is and try running it in the database

But you should use

where StaffID in (<cfqueryparam value="#idList#" list="yes" cfsqltype="cf_sql_integer">)

Ken

[MCL97]

Ken,
You are right. It works now. I am not sure why it didn't before. I must have mistyped or something. Thanks for going through my code.

Sincerely,
Min