选择IN超过2100个值 [英] Select IN on more than 2100 values

问题描述

如何在超过2100个值上执行select？

 < cfquery name =result.qryData > 
 SELECT sub_acct_no，... 
 FROM dbo.Closed_ORDER 
 WHERE ord_no IN< cfqueryparam cfsqltype =CF_SQL_varcharvalue =＃ValueList（qryOrd.ord_no）＃list =yes > 
< / cfquery> 
  

由于表的设置方式，链接服务器和JOINS不是一个选项。

解决方案

当这个被运行时会抛出一个错误，因为有很多字段被传入。 / div>

首先将值加载到XML中

 < cfset var strResult ='< ul class =xoxo >'> 
< cfloop query =qryOrd> 
< cfset strResult& ='< li> #xmlformat（ord_no）＃< / li>'& 
< / cfloop> 
< cfset strResult& ='< / ul>'> 
  

然后在sql查询中使用xml

 < cfquery name =result.qryData> 
 DECLARE @xmlOrd_no xml =< cfqueryparam cfsqltype =CF_SQL_varcharvalue =＃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> 
  

您也可以执行XML的转储，并将其格式设置为HTML

 < cfoutput>＃strResult＃< / cfoutput> 
  

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.

解决方案

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>

这篇关于选择IN超过2100个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！