CFQUERYPARAM and IN/NOT IN clauses
I discovered today, to my unpleasant surprise, that there is apparently a limit to the number of elements there can be in an SQL IN/NOT IN clause using cfqueryparam. I’m not sure of the limit, but I had an IN clause with some 5,898 primary keys in it, and I consistently got this exception from CF:
The DBMS returned an unspecified error.
This suggests to me that perhaps the JDBC driver or MS SQL Server 2005 itself has a limit on the number of items in an IN clause. This post at HouseOfFusion suggests that Oracle has a limit of 1,000 items, and that MS SQL Server’s limit is something around 2,100 items.
My workaround was to loop over the list of 5,898 items individually, like this:
WHERE ID IN (
<cfloop list="#Arguments.IDs#" index="id">
#id#<cfif id neq ListLast(Arguments.ID)>,</cfif>
</cfloop>
)
It’s not the greatest thing ever, I know. Hopefully I can use <cfqueryparam/> instead of the raw value in it. That’ll be a test for later.
tags: cf, coldfusion, programming, sql, microsoft, t-sql
This is a fundamental limit for databases.
SQL is not designed for large in-line datasets.
You are abusing IN. When you run into this problem it is a ‘bad smell’ coming from your DB schema, time for a refactor.
If you really need more items than that then you can use a temporary table, insert all the values into it, and then use a select inside of the IN. Selects are not constrained by the in-line data limit.
Jon
May 3, 2007 at 6:44 pm