Select IN on more than 2100 values

Question

How can you do a select in on more than 2100 values?

<cfquery name="result.qryData">
  SELECT    sub_acct_no,  ...
  FROM  dbo.Closed_ORDER
  WHERE ord_no IN <cfqueryparam cfsqltype="CF_SQL_varchar" value="#ValueList(qryOrd.ord_no)#" list="yes">
</cfquery>

Because of the ways that the tables are setup, linked Servers and JOINS are not an option.

When this is ran an error this thrown because there are new many fields being passed in.

Silverside

Every SQL system I've worked with limits the use of IN to, typically, about 1000 values. The way to do this is create a table - temporarily if need be - and use a JOIN. If nothing else it's a whole lot faster than an IN clause. – Labio

Sounds like you may need to reevaluate your table structure if JOINs are not an option. – Jacquejacquelin

Joins are not an option because the source DB and the target DB are on two different servers, and they won't allow linked servers – Silverside

Answer 1

First load the values into XML

<cfset var strResult = '<ul class="xoxo">'>
<cfloop query="qryOrd">
    <cfset strResult &= '<li>#xmlformat(ord_no)#</li>'>
</cfloop>
<cfset strResult &= '</ul>'>

Then use the xml in the sql query

<cfquery name="result.qryData">
DECLARE @xmlOrd_no       xml = <cfqueryparam cfsqltype="CF_SQL_varchar" value="#strResult#">


DECLARE @tblOrd_no          TABLE (ID varchar(20))


INSERT INTO @tblOrd_no
SELECT tbl.Col.value('.', 'varchar(20)')
FROM    @xmlOrd_no.nodes('/ul/li') tbl(Col)


SELECT  sub_acct_no,  ...
FROM    dbo.Closed_ORDER
WHERE   ord_no IN (SELECT ID FROM @tblOrd_no)
</cfquery>

You can also do a dump of the XML and it is properly formatted in HTML

 <cfoutput>#strResult#</cfoutput>

Silverside

FYI, another alternative is using WITH instead of the extra table variable. I would also the execution plan, to see whether a JOIN or IN is faster. – Ephram

Bah.. terrible typing. I meant "check the execution plan" to see which is more efficient. – Ephram