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

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>