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>
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