How to get all records using IN operator in cfqueryparam in coldfusion?

[abcd987]

I have a query which I try to fetch records using IN operator. But the record retrieves only first data not all data . What I have tried is

<cfargument name="names" type="struct" >
SELECT
ID,
Name,
Title
FROM
tblabcd
WHERE
Active = 1
AND Name IN(<cfqueryparam value="#arguments.names.data#" cfsqltype="cf_sql_varchar" list="yes" separator="|">)
</cfquery>

Here arguments.names.data = abc data | efg name

Query return only data of "abc data"

How to return both data's from this query using IN operator.

[Dave Watts]

What happens if you just put in "efg name" as a test value in place of the CFQUERYPARAM? Do you get any matching records?

Dave Watts, Eidolon LLC 

[abcd987]

Yes

[BKBK]

You have to trim each list element to remove any extra space. 

<!--- Before the query tag, trim each list item of arguments.names.data --->
<cfscript>    
       closure=function(listItem){ 
                   return trim(listItem); // Trims each list item
               }
       // New list without excess spaces
       trimmedList=listMap(arguments.names.data,closure,"|");
</cfscript>

<!--- Within the query --->
<cfqueryparam value="#trimmedList#" cfsqltype="cf_sql_varchar" list="yes" separator="|">

[Dave Watts]

Good catch!

Dave Watts, Eidolon LLC 

[abcd987]

Not Working

[BKBK]

Not Working

By @abcd987

Could you tell us a bit more? What isn't working? Do you get an error? If not, what you get when you dump the query?

[abcd987]

Not working

[BKBK]

Here is proof that the suggestion works. Just copy-paste it and run it as a CFM page.

<!--- Simulate the query --->
<cfscript>
  data = queryNew('ID,Name,Title,Active','integer,varchar,varchar,bit');

  queryAddRow(data,2);

  // Row 1
  querySetCell(data,'ID','1',1);    
  querySetCell(data,'Name','abc data',1);
  querySetCell(data,'Title','First Title',1);
  querySetCell(data,'Active',1,1);

  // Row 2
  querySetCell(data,'ID','2',2);  
  querySetCell(data,'Name','efg name',2);
  querySetCell(data,'Title','Second Title',2);
  querySetCell(data,'Active',1,2);
</cfscript>

<cfset arguments.names.data=" abc data | efg name ">

<!--- Trim each list item of arguments.names.data --->
<cfscript>    
       closure=function(listItem){ 
                   return trim(listItem); // Trims each list item
               };
       // New list without excess spaces
       trimmedList=listMap(arguments.names.data,closure,"|");
</cfscript>

<!--- Run the query --->
<cfquery name="activeData" dbtype="query">
SELECT
ID,
Name,
Title
FROM
data
WHERE
Active = 1
AND Name IN(<cfqueryparam value="#trimmedList#" cfsqltype="cf_sql_varchar" list="yes" separator="|">)
</cfquery>

<!--- Dump the query result --->
<cfdump var="#activeData#" label="Data for which Active=1">

The result is: