giving problem when a formatted string used with cfquery tag

Hi,

I have a search query returning a set of result, of which I am taking the filename. For each of this filename I need to run a SQL query to fetch a set of values from database (SQL Server 2000) . One of these fields can have values like "1,2,3" or "1" or "1,2" and so on. Each number is associated with a name and I need to display those names.

I have two tables as below

Table1

Each of the numbers in the list in Table1 correspond to a name in Table2

Table2

Below is the code:

<cfoutput query="searchQuery" STARTROW="#startRecord#" MAXROWS="10">   <!--- Output of search query --->

     <cfquery name="getlist" datasource="data">
          SELECT list FROM Table1 WHERE filename like '%#searchQuery.fileName#%'   <!--- list returns a value like 1,2,3 as explained above --->
     </cfquery>

     <!--- to get the names I need to build a query as below  --->

     <!--- SELECT name FROM Table2 where id IN ('1','2','3') --->

     <!--- I am able to build the string  '1', '2', '3' as shown below  --->

     <cfset temp_list = #getlist.list#>
     <cfparam name="idlist" default="">
               
      <cfif len(#temp_list#) GT 1>
           <cfloop list="#temp_list#" index="i" delimiters=",">
                        <cfset idlist= idlist& " ' " &i& " ', ">   <!--- This builds string like '1','2','3' from 1,2,3 as in the table  --->
           </cfloop>
           <cfset idlist= left(idlist, len(idlist)-1)>

     </cfif>

     <cfoutput>SELECT name FROM Table2  where id IN (#idlist#)</cfoutput>

     <!--- The above query is built like SELECT name FROM Table2  where id IN ('1','2','3') , exactly the way I want--->

     <cfquery name="getname" datasource="portal">
          SELECT name FROM Table2  where id IN (#idlist#)
     </cfquery>

     <!--- The above query within cfquery tag is built like SELECT name FROM Table2  where id IN (''1'',''2'',''3'') , its not double quote but two single quotes, which is causing SQL exception --->

Can anyone please let me know about this behaviour within cfquery tag and any solution to fix this.

The error points to line SELECT statement within cfquery tag.

Below are the few lines of the stack trace.

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '1'.
     at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
     at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
     at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)

Thanks

Nikhil