## Tuesday, September 5, 2006

### 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: , , , , ,