blocks|key|445214|text|通常的方法是将IN子句作为子查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|445215|SELECT+ProductID+from+Product+where+ManufacturerID+in+
++++(SELECT+ManufacturerID+from+Manufacturer+where+HomeOfficeLocation+=+'France')|code-block|syntax|javascript|445216|如果您不能做到这一点:只要用户从您提供的列表中进行选择，简单的连接列表就不会特别容易出错。|445217|您肯定不希望循环遍历大型数据集来查找少量的项。|445218|编辑以添加|offset|length|style|BOLD|445219|说得更完整一点:我经常做的是在前端创建一个逗号分隔的列表(例如："234,+345,+456")并将其作为字符串传递给存储过程；然后在该过程中使用用户定义函数，例如讨论的here函数。|CODE|445220|我不反对临时表，但通常只有当我要对列表执行多个操作时才会使用临时表。|445221|我之所以说“只要用户是从列表中挑选的”，是为了省去你验证(和清理)用户输入的麻烦。|445222|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2872358/sql-split-function^0|0|0|0|0|2|3|0|W|F|2D|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|17|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|18|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|19|8|@$M|1A|N|1B|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1C|8|@$M|1D|N|1E|O|S]]|9|@$M|1F|N|1G|1|1H]]|A|$]]|$1|T|3|U|5|6|7|1I|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1J|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|14]]]]

The usual way is to make the IN clause a subquery:

<pre><code>SELECT ProductID from Product where ManufacturerID in 
 (SELECT ManufacturerID from Manufacturer where HomeOfficeLocation = 'France')
</code></pre>

If you can't do that: as long as the user is picking from a list you've provided, a simple concatenated list isn't particularly error-prone.

You definitely don't want to loop through a large dataset looking for a small number of items.

Edited to add

To be a bit more complete: what I often do is create a comma-delimited list in the front end (ex. <code>"234, 345, 456"</code>) and pass that to a stored procedure as a string; then inside that proc use a user-defined function such as the one discussed <a href="https://stackoverflow.com/questions/2872358/sql-split-function">here</a>.

I have nothing against temp tables, but usually I only use them if I'm going to do more than one operation on a list.

The reason I said "as long as the user is picking from a list" is to save you having to validate (and sanitize) user input.

blocks|key|4483498|text|如果in子句中的项数足够大，则可能需要使用临时表-首先在那里插入值，然后连接到临时表，或者将其用作SELECT的子查询源。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4483499|此CodeProject+article讨论了临时表的使用；但总结一下您的情况：|offset|length|4483500|CREATE+TABLE+#TempQueryList(Item+varchar(300))

//+in+your+app+code:
insert+into+#TempQueryList+values+(+@item+);+--+use+BIND+variable
insert+into+#TempQueryList+values+(+@item+);+--+use+BIND+variable
...
insert+into+#TempQueryList+values+(+@item+);+--+use+BIND+variable

//+then+your+query+becomes:
SELECT+
++ID,+Name
FROM
++MyTable
WHERE
++SomeValue+IN+(SELECT+Item+FROM+#TempQueryList)|code-block|syntax|javascript|4483501|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005^0|0|1|J|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@$D|U|E|V|1|W]]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

If the number of items in your IN clause is large enough, you probably want to use a temporary table - insert the values there first then join to the temp table, or use it as a subquery source for your SELECT.

This <a href="http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005" rel="nofollow">CodeProject article</a> discusses the use of temp tables; but to summarize for your case:

<pre><code>CREATE TABLE #TempQueryList(Item varchar(300))

// in your app code:
insert into #TempQueryList values ( @item ); -- use BIND variable
insert into #TempQueryList values ( @item ); -- use BIND variable
...
insert into #TempQueryList values ( @item ); -- use BIND variable

// then your query becomes:
SELECT 
 ID, Name
FROM
 MyTable
WHERE
 SomeValue IN (SELECT Item FROM #TempQueryList)
</code></pre>

blocks|key|1474733|text|如果您可以使用子查询(如egrunin所示)完成此操作，那么这将是最简单、最快的。如果你不能做到这一点(例如，你得到的只是一个值列表)，那么你可能想要查看一个表值参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1474734|This+question有一些读物可能会让你感兴趣。|offset|length|1474735|entityMap|0|LINK|mutability|MUTABLE|url|https://dba.stackexchange.com/q/629/26^0|0|0|D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

If you can do it with a subquery (as egrunin showed), that's easiest and fastest. If you can't do that (ie, all you've got is a list of values), then you might want to look at a table value parameter.

<a href="https://dba.stackexchange.com/q/629/26">This question</a> has some reading that might be of interest.

blocks|key|455212|text|以下是使用xml完成此操作的一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|455213|C#：|455214|StringBuilder+sb+=+new+StringBuilder();
sb.AppendLine("<SelectedValues>");

foreach+(string+s+in+userSelectedValues)
++sb.AppendFormat("<row+val=\"{0}\"+/>{1}",+s,+Environment.NewLine);
sb.AppendLine("</SelectedValues>");|code-block|syntax|javascript|455215|将此sb.ToString()作为XML传递给存储过程|455216|SQL：|455217|CREATE+PROCEDURE+dbo.MyStoredProcedure
(
++@UserSelectedValuesXml+XML
)+++++

SELECT+*+
FROM+dbo.MyTable
WHERE+Id+IN+(+SELECT+ref.value('@val',+'varchar')
++++++++++++++FROM+@UserSelectedValuesXml.nodes('/SelectedValues/row')+AS+T(ref))|455218|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Here is one way to do it using xml:

C#:

<pre><code>StringBuilder sb = new StringBuilder();
sb.AppendLine("&lt;SelectedValues&gt;");

foreach (string s in userSelectedValues)
 sb.AppendFormat("&lt;row val=\"{0}\" /&gt;{1}", s, Environment.NewLine);
sb.AppendLine("&lt;/SelectedValues&gt;");
</code></pre>

Pass this sb.ToString() AS XML TO your stored procedure

SQL:

<pre><code>CREATE PROCEDURE dbo.MyStoredProcedure
(
 @UserSelectedValuesXml XML
) 

SELECT * 
FROM dbo.MyTable
WHERE Id IN ( SELECT ref.value('@val', 'varchar')
 FROM @UserSelectedValuesXml.nodes('/SelectedValues/row') AS T(ref))
</code></pre>

blocks|key|445411|text|这个主题将在本系列文章中详细讨论：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|445412|445413|Arrays+and+Lists+in+SQL+Server+2008+Using+Table-Valued+Parameters|unordered-list-item|offset|length|445414|Arrays+and+Lists+in+SQL+Server+2005+and+Beyond+When+TVPs+Do+Not+Cut+it|445415|Arrays+and+Lists+in+SQL+Server+2000+and+Earlier|445416|445417|您应该从第一篇文章使用Table+Value+Parameter中推荐的方法开始。另请参阅MSDN用法文章：Table-Valued+Parameters+in+SQL+Server+2008+(ADO.NET)|445418|void+SelectValuesOfInterest(IEnumerable<SqlDataRecord>+valuesOfInterest)
SqlCommand+cmd+=+new+SqlCommand(@"++++SELECT+
++++++++ID,+Name
++++FROM
++++++++MyTable
++++WHERE
++++++++SomeValue+IN+(SELECT+value+FROM+@tableValueParameter);"
,+connection);
SqlParameter+tvpParam+=+insertCommand.Parameters.AddWithValue(
++++"@tableValueParameter",+valuesOfInterest);
tvpParam.SqlDbType+=+SqlDbType.Structured;
tvpParam.TypeName+=+"dbo.MyTVPType";
using+(SqlDataReader+rdr+=+cmd.ExecuteReader+())
{
+while+rdr.Read()+{...}
}
}|code-block|syntax|javascript|445419|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sommarskog.se/arrays-in-sql-2008.html|1|http://www.sommarskog.se/arrays-in-sql-2005.html|2|http://www.sommarskog.se/arrays-in-sql-2000.html|3|http://msdn.microsoft.com/en-us/library/bb510489.aspx|4|http://msdn.microsoft.com/en-us/library/bb675163.aspx^0|0|0|0|1T|0|0|0|1Y|1|0|0|1B|2|0|0|B|L|3|1I|1G|4|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1B|8|@]|9|@$F|1C|G|1D|1|1E]]|A|$]]|$1|H|3|I|5|E|7|1F|8|@]|9|@$F|1G|G|1H|1|1I]]|A|$]]|$1|J|3|K|5|E|7|1J|8|@]|9|@$F|1K|G|1L|1|1M]]|A|$]]|$1|L|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1O|8|@]|9|@$F|1P|G|1Q|1|1R]|$F|1S|G|1T|1|1U]]|A|$]]|$1|O|3|P|5|Q|7|1V|8|@]|9|@]|A|$R|S]]|$1|T|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|U|$V|$5|W|X|Y|A|$Z|10]]|11|$5|W|X|Y|A|$Z|12]]|13|$5|W|X|Y|A|$Z|14]]|15|$5|W|X|Y|A|$Z|16]]|17|$5|W|X|Y|A|$Z|18]]]]

The topic is discussed at length in this series of articles:

<ul>
<li><a href="http://www.sommarskog.se/arrays-in-sql-2008.html" rel="nofollow">Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters</a></li>
<li><a href="http://www.sommarskog.se/arrays-in-sql-2005.html" rel="nofollow">Arrays and Lists in SQL Server 2005 and Beyond When TVPs Do Not Cut it</a></li>
<li><a href="http://www.sommarskog.se/arrays-in-sql-2000.html" rel="nofollow">Arrays and Lists in SQL Server 2000 and Earlier</a></li>
</ul>

You should start with the approach recommended in the first article, using a <a href="http://msdn.microsoft.com/en-us/library/bb510489.aspx" rel="nofollow">Table Value Parameter</a>. See also the MSDN usage article: <a href="http://msdn.microsoft.com/en-us/library/bb675163.aspx" rel="nofollow">Table-Valued Parameters in SQL Server 2008 (ADO.NET)</a>:

<pre><code>void SelectValuesOfInterest(IEnumerable&lt;SqlDataRecord&gt; valuesOfInterest)
SqlCommand cmd = new SqlCommand(@" SELECT 
 ID, Name
 FROM
 MyTable
 WHERE
 SomeValue IN (SELECT value FROM @tableValueParameter);"
, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
 "@tableValueParameter", valuesOfInterest);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.MyTVPType";
using (SqlDataReader rdr = cmd.ExecuteReader ())
{
 while rdr.Read() {...}
}
}
</code></pre>

blocks|key|455251|text|我会用table+value+parameter。根据您有权访问的SQL+server版本，您可以将整个datatable传递到存储过程中，然后像查询普通表一样对其进行查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|455252|基本上，您需要在C#中创建一个包含列表的表。您可以将其作为参数传递给存储过程。在这个过程中，您只需在MyTable和您传递的参数之间进行内连接，即可获得您想要的结果。|style|CODE|455253|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb510489.aspx^0|3|L|0|0|1E|7|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@$A|U|B|V|F|G]]|9|@]|C|$]]|$1|H|3|-4|5|6|7|W|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

I'd use a <a href="http://msdn.microsoft.com/en-us/library/bb510489.aspx" rel="nofollow">table value parameter</a>. Depending on the version of SQL server you have access to you can pass an entire datatable into a stored procedure then query off of it just like you would a normal table.

Basically, you'd create a table in C# that contains your list. You'd pass that as a parameter to a stored procedure. In the proc you could just do an inner join between <code>MyTable</code> and the parameter you passed to get the results you want.

My problem is simple in nature- query a database, and write output to a set of files (~1000).

I have a SQL query I need to execute from C# on SQL Server. The query is fairly simple except for one of the values in an IN clause.

As an example, the query may look like:

<pre><code>SELECT 
 ID, Name
FROM
 MyTable
WHERE
 SomeValue IN (/* list of values */)
</code></pre>

The list of values is around 300 strings that are 6 characters each. The values will ultimately look something like ("A00001", "A00002", etc), and are specified as input by the user of the application.

My initial thought was to build the query out as a long string, with each of the values aggregated together. This doesn't seem like the proper approach however, and seems to be error prone. My next thought was to use a stored procedure, but I'm unsure of how the data should be passed into it. How should this query be built in an efficient way where the data can be reliably passed in?

My other option is to read in ALL data from SQL Server and then filter the results in C# as I receive them. If I do this, however, I'll get around 9 million records which seems to be too many to read into memory as a data set. Because I'm writing files after parsing ~5000 records, I don't want to keep a data reader connected to the database while I chew through the data I'm receiving.

What is the best approach?

C# and long SQL Server queries, what's the correct way to do this?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的问题本质上很简单--查询一个数据库，并将输出写入一组文件(~1000)。我有一个SQL查询需要从SQL Server上的C#执行。除了in子句中的一个值之外，查询相当简单。例如，查询可能如下所示：SELECT     ID, NameFROM    MyTableWHERE    SomeValue IN (/* ...

问C#和长SQL Server查询，正确的方法是什么？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问C#和长SQL Server查询，正确的方法是什么？EN