SELECT sub_acct_no, ... FROM dbo.Cl...">
Geek Answers Handbook

Select IN for more than 2100 values

How can you select 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>

Due to the way tables are configured, connected servers and JOINS are not parameters.

When this error is executed, an error occurs because a lot of new ones are transferred to it.

+1
source share
1 answer

First load the values ​​in XML

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

Then use xml in 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 dump XML and format it correctly in HTML

  <cfoutput>#strResult#</cfoutput>
+4
source

More articles:


All Articles