Hi All Hope someone can be of assistance! I am setting a ValueList containing on a form in a cfm page to send over to a cfc to perform. The list contains ID's which are text. When they get passed to a CFC in an argument, I need to use them to do a filter in the SQL. The list may have one or more ID's so I need a way of splitting them so I can compare and use the AND condition for each of the ID's in turn. Also after I split them I need to add the AND operator between them Hope this makes sense! Or is there any easier way to do what I am trying to do? <!--- CFM PAGE ---> <!--- This gets set on the form. Looks over a query and gets a list of ownerIDS ---> cfset ownerList=ValueList(somequery.ownerID) <!--- CFC which takes in the ownerList as an argument from the form ---> <!--- Ownerlist coming from the foem = '123456,7891011' ---> cffunction name="getAllOwners" returnType="query" output="false" cfargument name="accountID" type="string" required="true" cfargument name="ownerList" type="string" required="true" cfquery name="getOwnerList" SELECT dbo.owner.OwnerID FROM dbo.owner WHERE (dbo.owner.OwnerID != '123456' AND dbo.owner_OwnerID != '7891011') /cfquery /cffunction

This is what SQL

IN

is for and you can use it with the

list

parameter of

cfqueryparam

🙄 of course duh! Forgot all about that 😂. Thanks @aliaspooryorik

Something to be aware of when using IN and queryparam in list mode: SQL imposes a limit of 2,100 total parameters in a query. It's very rare to ever get to that point, but if you think it could ever get there, you might want to consider a way around it. Apparently the "Entity Framework" does not impose this limitation, but I do not know anything about that.

Hmm interesting 🤨 any way around it?

When we run into it, we use a function iter_varcharlist_to_tbl, and pass our entire string into that function as one param. Then we do

IN (SELECT [string] FROM dbo.iter_varcharlist_to_tbl(...))

Also var that

getOwnerList

variable. It's currently leaking out of the function. Suspect you also wanna return something 😉

Myka, so your array would be something like...

local.sqlArray = [
"id = 123456",
"id = 654321"
]

and then you would do...

QueryExecute(
"
    SELECT *
    FROM table
    WHERE (:sqlConditions)
", {
    sqlConditions = {cfsqltype="cf_sql_varchar", value=ArrayToList(local.sqlArray, " OR ")}
},
{datasource="..."}
);

or something like that?

@James Harris, I had to rethink that, and what I was initially thinking doesn't actually get around the parameter limit.

That's what I was thinking. Feeding the array into cfqueryparam would still be one param per value.

@James Harris works brilliantly 👍 👍

Taking a step back, I see you have

<cfset ownerList=ValueList(somequery.ownerID)>

so you are getting the values from a database anyway? I guess the same database - so why not just do it all in one query and then you don't need to pass around 1,000s of values

@aliaspooryorik ah yes good call indeed 👍🙏