sqlcoldfusionqoq

Filtering a list of names from query

I have a query something like:

SELECT * 
FROM qAll 
WHERE name not in('Alina,Charaidew,Sukapha')

which is not working. What will be the best way to do so? As this list a generated dynamically and maybe different every time.

Solution

  • In CF, You should use cfqueryparam to for your query parameteres. To pass a list as parameter, you should add list attribute to the cfqueryparam. Your query should be similar to below: 

    <cfset nameList = "Alina,Charaidew,Sukapha">
<cfquery name="queryName" datasource="#Application.ds#">
        SELECT * FROM qAll WHERE name NOT IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" list="Yes" value="#nameList#" >)    
</cfquery>