参数化 SQL IN 子句

可以将参数作为字符串传递

所以你有字符串

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

然后，您所要做的就是将字符串作为 1 个参数传递。

这是我使用的拆分函数。

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

我将传递一个表类型参数（因为它是 SQL Server 2008），并执行 或内部连接。您还可以使用 XML，使用 ，然后为该临时表建立索引。where existssp_xml_preparedocument

对于像这样数量可变的参数，我知道的唯一方法是显式生成 SQL，或者执行一些涉及使用所需项目填充临时表并连接到临时表的操作。

这很恶心，但如果保证你至少有一个，你可以做：

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

具有 IN（ 'tag1'， 'tag2'， 'tag1'， 'tag1'， 'tag1'） 将很容易被 SQL Server 优化掉。此外，您还可以获得直接的索引搜索

您可以参数化每个值，如下所示：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

这将为您提供：

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

不，这不对 SQL 注入开放。唯一注入到 CommandText 中的文本不是基于用户输入的。它仅基于硬编码的“@tag”前缀和数组的索引。索引将始终为整数，不是用户生成的，并且是安全的。

用户输入的值仍然被塞入参数中，因此不存在漏洞。

编辑：

撇开注入问题不谈，请注意，构造命令文本以容纳可变数量的参数（如上所述）会阻碍 SQL Server 利用缓存查询的能力。最终的结果是，几乎可以肯定的是，你首先失去了使用参数的价值（而不是仅仅将谓词字符串插入到SQL本身中）。

并不是说缓存的查询计划没有价值，但 IMO 这个查询还不够复杂，无法从中看到很多好处。虽然编译成本可能接近（甚至超过）执行成本，但你仍然在谈论毫秒。

如果您有足够的 RAM，我希望 SQL Server 也可能会缓存一个常见参数计数的计划。我想你总是可以添加五个参数，并让未指定的标签为 NULL - 查询计划应该是相同的，但这对我来说似乎很丑陋，我不确定它是否值得进行微优化（尽管，在 Stack Overflow 上 - 它可能非常值得）。

此外，SQL Server 7 及更高版本将自动参数化查询，因此从性能角度来看，使用参数并不是真正必要的 - 但是，从安全角度来看，这至关重要 - 尤其是对于这样的用户输入数据。

这是我使用过的一种快速而肮脏的技术：

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

所以下面是 C# 代码：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

有两点需要注意：

• 表现很糟糕。 查询未编制索引。LIKE "%...%"
• 确保您没有任何 、空白或 null 标记，否则这将不起作用|

还有其他方法可以做到这一点，有些人可能会认为更清洁，所以请继续阅读。

对于 SQL Server 2008，可以使用表值参数。这需要一些工作，但可以说它比我的其他方法更干净。

首先，您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后，您的 ADO.NET 代码如下所示：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

更新根据@Doug

请尽量避免var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);

它设置了第一个值长度，因此如果第一个值为 3 个字符，则其设置的最大长度为 3，如果超过 3 个字符，则其他记录将被截断。

因此，请尝试使用：var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, maxLen);

注意：对于最大长度。-1

这可能是一种令人讨厌的方式，我用过一次，相当有效。

根据您的目标，它可能会有用。

1. 创建一个包含一列的临时表。
2. INSERT每个查找值添加到该列中。
3. 然后，您可以只使用标准规则，而不是使用 。（ 灵活性++ ）INJOIN

这在您可以执行的操作方面增加了一些灵活性，但它更适合于以下情况：要查询一个大型表，具有良好的索引，并且希望多次使用参数化列表。节省了执行两次和手动完成所有卫生工作的时间。

我从来没有想过它到底有多快，但在我的情况下，它是需要的。

我们有一个函数，可以创建一个表变量，你可以加入到这个变量中：

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

所以：

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

在 ColdFusion 中，我们只执行以下操作：

<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
    <cfquery name="q">
        select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
    </cfquery>

我今天在播客上听到了 Jeff/Joel 谈论这个问题（第 34 集，2008-12-16 （MP3,31 MB），1 小时 03 分 38 秒 - 1 小时 06 分 45 秒），我想我记得 Stack Overflow 使用了 LINQ to SQL，但也许它被放弃了。LINQ to SQL 中也是如此。

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

就是这样。而且，是的，LINQ 已经足够向后看了，但这个子句对我来说似乎更向后。当我不得不在工作中对一个项目进行类似的查询时，我自然而然地试图通过在本地数组和 SQL Server 表之间进行联接来以错误的方式执行此操作，从而确定 LINQ to SQL 转换器是否足够聪明，可以以某种方式处理转换。它没有，但它确实提供了一条描述性的错误消息，并指出我使用 Contains。Contains

无论如何，如果在强烈推荐的 LINQPad 中运行此查询并运行此查询，则可以查看 SQL LINQ 提供程序生成的实际 SQL。它将显示参数化为子句的每个值。IN

恕我直言，正确的方法是将列表存储在字符串中（长度受 DBMS 支持的长度限制）;唯一的诀窍是（为了简化处理）我在字符串的开头和结尾有一个分隔符（在我的示例中为逗号）。这个想法是“动态规范化”，将列表变成一个单列表，每个值包含一行。这允许您转动

在（CT1，CT2，CT3 ...ctn）

变成

在（选择...）中

或者（我可能更喜欢的解决方案）常规连接，如果您只是添加“distinct”以避免列表中出现重复值的问题。

不幸的是，对字符串进行切片的技术是相当特定于产品的。 以下是 SQL Server 版本：

 with qry(n, names) as
       (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
               substring(list.names, 2, len(list.names)) as names
        from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
        union all
        select (n - 1) as n,
               substring(names, 1 + charindex(',', names), len(names)) as names
        from qry
        where n > 1)
 select n, substring(names, 1, charindex(',', names) - 1) dwarf
 from qry;

Oracle 版本：

 select n, substr(name, 1, instr(name, ',') - 1) dwarf
 from (select n,
             substr(val, 1 + instr(val, ',', 1, n)) name
      from (select rownum as n,
                   list.val
            from  (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
                   from dual) list
            connect by level < length(list.val) -
                               length(replace(list.val, ',', ''))));

和 MySQL 版本：

select pivot.n,
      substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
     union all
     select 2 as n
     union all
     select 3 as n
     union all
     select 4 as n
     union all
     select 5 as n
     union all
     select 6 as n
     union all
     select 7 as n
     union all
     select 8 as n
     union all
     select 9 as n
     union all
     select 10 as n) pivot,    (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n <  length(list.val) -
                   length(replace(list.val, ',', ''));

（当然，“pivot”必须返回与最大行数一样多的行数 我们可以在列表中找到的项目）

最初的问题是“如何参数化查询......”

这不是对最初问题的回答。在其他答案中，有一些非常好的演示如何做到这一点。

参见 Mark Brackett 的第一个答案（第一个答案以“您可以参数化每个值”开头）和 Mark Brackett 的第二个答案，这是我（和其他 231 人）投赞成票的首选答案。他的回答中给出的方法允许 1） 有效使用绑定变量，以及 2） 用于可优化的谓词。

已选答案

我在这里讨论的是乔尔·斯波尔斯基（Joel Spolsky）的答案中给出的方法，即“选择”的答案作为正确答案。

乔尔·斯波尔斯基（Joel Spolsky）的方法很聪明。它的工作方式是合理的，它将表现出可预测的行为和可预测的性能，给定“正常”值，并具有规范的边缘情况，例如 NULL 和空字符串。对于特定应用来说，这可能就足够了。

但是，在推广这种方法时，我们还要考虑更晦涩的极端情况，例如当列包含通配符时（由 LIKE 谓词识别）。我看到最常用的通配符是（百分号）。因此，让我们现在在这里处理这个问题，稍后再讨论其他情况。Name%

% 字符的一些问题

考虑 Name 值 。（对于此处的示例，我使用文本字符串值代替列名。Name 值为 ''pe%ter' 的行将由以下格式的查询返回：'pe%ter'

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但是，如果搜索词的顺序颠倒，则不会返回同一行：

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我们观察到的行为有点奇怪。更改列表中搜索词的顺序会更改结果集。

不言而喻，无论他多么喜欢花生酱，我们可能都不想搭配花生酱。pe%ter

晦涩难懂的极端情况

（是的，我同意这是一个晦涩难懂的案例。可能是一个不太可能被测试的。我们不希望列值中有通配符。我们可以假设应用程序阻止了此类值的存储。但根据我的经验，我很少看到数据库约束明确禁止在比较运算符右侧被视为通配符的字符或模式。LIKE

修补漏洞

修补此漏洞的一种方法是转义通配符。（对于不熟悉运算符上的转义子句的任何人，这里是指向 SQL Server 文档的链接。%

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

现在我们可以匹配文字 %。当然，当我们有一个列名时，我们将需要动态转义通配符。我们可以使用该函数来查找该字符的出现次数，并在每个字符前面插入一个反斜杠字符，如下所示：REPLACE%

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

这样就解决了 % 通配符的问题。几乎。

逃脱逃脱

我们认识到，我们的解决方案带来了另一个问题。转义字符。我们看到，我们还需要转义任何转义角色本身的出现。这一次，我们使用 ！作为转义字符：

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

下划线也是

现在我们开始了，我们可以添加另一个句柄，即下划线通配符。为了好玩，这一次，我们将使用 $ 作为转义字符。REPLACE

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我更喜欢这种转义方法，因为它适用于 Oracle 和 MySQL 以及 SQL Server。（我通常使用 \ 反斜杠作为转义字符，因为这是我们在正则表达式中使用的字符。但为什么要受制约呢！

那些讨厌的括号

SQL Server 还允许通过将通配符括在括号中来将通配符视为文本。因此，我们还没有完成修复，至少对于 SQL Server 而言是这样。由于成对的括号具有特殊的含义，因此我们也需要转义它们。如果我们设法正确地转义括号，那么至少我们不必为括号内的连字符和克拉而烦恼。我们可以将括号内的任何 and 字符转义，因为我们基本上已经禁用了括号的特殊含义。[]-^% _

找到匹配的括号对应该不难。这比处理单例 % 和 _ 的出现要困难一些。（请注意，仅转义所有出现的括号是不够的，因为单例括号被视为文本，不需要转义。逻辑变得有点模糊，超出了我在不运行更多测试用例的情况下所能处理的。

内联表达式变得混乱

SQL 中的内联表达式越来越长，越来越丑陋。我们也许可以让它起作用，但上天会帮助那些落后并必须破译它的可怜灵魂。尽管我是内联表达式的粉丝，但我倾向于不在这里使用内联表达式，主要是因为我不想发表评论来解释混乱的原因，并为此道歉。

其中的函数？

好的，所以，如果我们不将其作为 SQL 中的内联表达式处理，那么我们最接近的替代方案是用户定义的函数。我们知道这不会加快速度（除非我们可以在上面定义一个索引，就像我们在 Oracle 上所做的那样）。如果我们必须创建一个函数，我们最好在调用 SQL 语句的代码中执行此操作。

该函数在行为上可能存在一些差异，具体取决于 DBMS 和版本。（向所有热衷于能够互换使用任何数据库引擎的 Java 开发人员致敬。

领域知识

我们可能对列的域有专门的知识，（即为列强制执行的允许值集。我们可能先验地知道，存储在列中的值永远不会包含百分号、下划线或括号对。在这种情况下，我们只需快速评论这些情况已涵盖在内。

列中存储的值可能允许 % 或 _ 个字符，但约束可能要求对这些值进行转义，可能使用定义的字符，以便这些值与比较“安全”类似。同样，快速评论一下允许的值集，特别是哪个字符被用作转义字符，并遵循 Joel Spolsky 的方法。

但是，在没有专业知识和保证的情况下，对我们来说，至少要考虑处理那些晦涩难懂的极端情况，并考虑行为是否合理和“符合规范”。

---

概述的其他问题

我相信其他人已经充分指出了其他一些普遍关注的领域：

• SQL注入（获取看似用户提供的信息，并将其包含在SQL文本中，而不是通过绑定变量提供它们。使用绑定变量不是必需的，它只是阻止 SQL 注入的一种便捷方法。还有其他方法可以处理它：

• 优化器计划使用索引扫描而不是索引查找，可能需要表达式或函数来转义通配符（表达式或函数的可能索引）

• 使用文本值代替绑定变量会影响可伸缩性

---

结论

我喜欢乔尔·斯波尔斯基（Joel Spolsky）的方法。这很聪明。它起作用了。

但是我一看到它，就立即看到了它的潜在问题，让它滑落不是我的天性。我并不是要批评别人的努力。我知道许多开发人员都非常个人化地对待他们的工作，因为他们投入了大量资金，并且非常关心它。所以请理解，这不是人身攻击。我在这里确定的是生产中出现的问题类型，而不是测试中出现的问题类型。

另一种可能的解决方案是，不要将可变数量的参数传递给存储过程，而是传递一个包含您所追求的名称的字符串，但通过用“<>”将它们括起来使它们唯一。然后使用 PATINDEX 查找名称：

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

我认为在这种情况下，静态查询不是要走的路。动态构建 in 子句的列表，转义单引号，并动态构建 SQL。在这种情况下，由于列表很小，您可能不会看到任何方法有太大区别，但最有效的方法实际上是完全按照您的帖子中编写的 SQL 发送。我认为以最有效的方式编写它是一个好习惯，而不是做最漂亮的代码，或者认为动态构建 SQL 是不好的做法。

我看到在许多情况下，拆分函数的执行时间比查询本身要长，因为参数会变大。在 SQL 2008 中，具有表值参数的存储过程是我唯一考虑的其他选项，尽管在您的情况下这可能会更慢。如果您搜索 TVP 的主键，则 TVP 可能只会对大型列表更快，因为 SQL 无论如何都会为列表构建一个临时表（如果列表很大）。除非你测试它，否则你不会确定它。

我还看到存储过程有 500 个参数，默认值为 null，并且 WHERE Column1 IN （@Param1， @Param2， @Param3， ...， @Param500）。这导致 SQL 构建一个临时表，执行排序/非重复，然后执行表扫描而不是索引查找。这基本上就是你通过参数化该查询来做的事情，尽管规模足够小，不会产生明显的差异。我强烈建议不要在您的 IN 列表中使用 NULL，因为如果它被更改为 NOT IN，它将无法按预期运行。您可以动态构建参数列表，但唯一显而易见的是，对象将为您转义单引号。这种方法在应用程序端也稍慢一些，因为对象必须解析查询才能找到参数。在 SQL 上，它可能会更快，也可能不会更快，因为参数化查询会调用 sp_prepare，sp_execute执行查询的次数，然后是 sp_unprepare。

重用存储过程或参数化查询的执行计划可能会提高性能，但它会将您锁定在由执行的第一个查询确定的一个执行计划中。在许多情况下，对于后续查询来说，这可能不太理想。在您的情况下，重用执行计划可能是一个加分项，但它可能根本没有任何区别，因为该示例是一个非常简单的查询。

克利夫斯指出：

就您的情况而言，您所做的任何事情，无论是使用列表中固定数量的项进行参数化（如果未使用则为 null）、动态生成带或不带参数的查询，还是使用具有表值参数的存储过程都不会有太大区别。但是，我的一般建议如下：

您的案例/简单查询，参数很少：

动态 SQL，如果测试显示性能更好，则可能带有参数。

具有可重用执行计划的查询，只需更改参数或查询复杂即可多次调用：

具有动态参数的 SQL。

具有大型列表的查询：

具有表值参数的存储过程。如果列表可能相差很大，则在存储过程中使用 WITH RECOMPILE，或者直接使用不带参数的动态 SQL 为每个查询生成新的执行计划。

在我看来，解决这个问题的最佳来源是本网站上发布的内容：

系统注释。迪纳卡尔·内西

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

用：

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

学分：迪纳卡尔·内西

唯一的制胜法宝就是不玩。

对你来说没有无限的可变性。只有有限的可变性。

在 SQL 中，您有一个这样的子句：

and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )

在 C# 代码中，您可以执行如下操作：

  int origCount = idList.Count;
  if (origCount > 5) {
    throw new Exception("You may only specify up to five originators to filter on.");
  }
  while (idList.Count < 5) { idList.Add(-1); }  // -1 is an impossible value
  return ExecuteQuery<PublishDate>(getValuesInListSQL, 
               origCount,   
               idList[0], idList[1], idList[2], idList[3], idList[4]);

所以基本上，如果计数为 0，那么就没有过滤器，一切都会通过。如果计数大于 0，则该值必须在列表中，但该列表已使用不可能的值填充为 5（因此 SQL 仍然有意义）

有时，蹩脚的解决方案是唯一真正有效的解决方案。

我有一个不需要UDF，XML的答案 因为 IN 接受 select 语句 例如，SELECT * FROM Test where Data IN （SELECT Value FROM TABLE）

您实际上只需要一种方法即可将字符串转换为表格。

这可以通过递归 CTE 或使用数字表（或 Master..spt_value）

这是 CTE 版本。

DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'

SELECT @InputString = @InputString + ','

;WITH RecursiveCSV(x,y) 
AS 
(
    SELECT 
        x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),
        y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))
    UNION ALL
    SELECT 
        x = SUBSTRING(y,0,CHARINDEX(',',y,0)),
        y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))
    FROM 
        RecursiveCSV 
    WHERE
        SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR 
        SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''
)
SELECT
    * 
FROM 
    Tags
WHERE 
    Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);

如果从 .NET 调用，则可以使用 Dapper dot net：

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

在这里，Dapper 会进行思考，因此您不必这样做。当然，LINQ to SQL 也可以实现类似的事情：

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

也许我们可以在这里使用 XML：

    declare @x xml
    set @x='<items>
    <item myvalue="29790" />
    <item myvalue="31250" />
    </items>
    ';
    With CTE AS (
         SELECT 
            x.item.value('@myvalue[1]', 'decimal') AS myvalue
        FROM @x.nodes('//items/item') AS x(item) )

    select * from YourTable where tableColumnName in (select myvalue from cte)

下面是一种重新创建要在查询字符串中使用的本地表的技术。这样做可以消除所有解析问题。

字符串可以用任何语言生成。在这个例子中，我使用了SQL，因为这是我试图解决的最初问题。我需要一种干净的方式，以字符串形式动态传入表数据，以便稍后执行。

使用用户定义的类型是可选的。创建类型仅创建一次，并且可以提前完成。否则，只需将完整表类型添加到字符串中的声明中即可。

一般模式易于扩展，可用于传递更复杂的表。

-- Create a user defined type for the list.
CREATE TYPE [dbo].[StringList] AS TABLE(
    [StringValue] [nvarchar](max) NOT NULL
)

-- Create a sample list using the list table type.
DECLARE @list [dbo].[StringList]; 
INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')

-- Build a string in which we recreate the list so we can pass it to exec
-- This can be done in any language since we're just building a string.
DECLARE @str nvarchar(max);
SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '

-- Add all the values we want to the string. This would be a loop in C++.
SELECT @str = @str + '(''' + StringValue + '''),' FROM @list

-- Remove the trailing comma so the query is valid sql.
SET @str = substring(@str, 1, len(@str)-1)

-- Add a select to test the string.
SET @str = @str + '; SELECT * FROM @list;'

-- Execute the string and see we've pass the table correctly.
EXEC(@str)

使用以下存储过程。它使用自定义拆分函数，可以在此处找到。

 create stored procedure GetSearchMachingTagNames 
    @PipeDelimitedTagNames varchar(max), 
    @delimiter char(1) 
    as  
    begin
         select * from Tags 
         where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter) 
    end

这是针对同一问题的解决方案的交叉帖子。比保留分隔符更可靠 - 包括转义数组和嵌套数组，并理解 NULL 和空数组。

C# & T-SQL 字符串 [] 打包/解包实用工具函数

然后，您可以联接到表值函数。

如果您有 SQL Server 2008 或更高版本，我将使用表值参数。

如果您不幸被困在 SQL Server 2005 上，您可以添加如下 CLR 函数：

[SqlFunction(
    DataAccessKind.None,
    IsDeterministic = true,
    SystemDataAccess = SystemDataAccessKind.None,
    IsPrecise = true,
    FillRowMethodName = "SplitFillRow",
    TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
        return new string[0];

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

你可以像这样使用，

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';

select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

如果我们在 IN 子句中存储了以逗号（，） 分隔的字符串，我们可以使用 charindex 函数来获取值。如果使用 .NET，则可以使用 SqlParameters 进行映射。

DDL 脚本：

CREATE TABLE Tags
    ([ID] int, [Name] varchar(20))
;

INSERT INTO Tags
    ([ID], [Name])
VALUES
    (1, 'ruby'),
    (2, 'rails'),
    (3, 'scruffy'),
    (4, 'rubyonrails')
;

T-SQL：T-SQL：

DECLARE @Param nvarchar(max)

SET @Param = 'ruby,rails,scruffy,rubyonrails'

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

可以在 .NET 代码中使用上述语句，并使用 SqlParameter 映射参数。

Fiddler 演示

编辑：使用以下脚本创建名为 SelectedTags 的表。

DDL 脚本：

Create table SelectedTags
(Name nvarchar(20));

INSERT INTO SelectedTags values ('ruby'),('rails')

T-SQL：T-SQL：

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

这是另一种选择。只需将逗号分隔的列表作为字符串参数传递给存储过程，然后：

CREATE PROCEDURE [dbo].[sp_myproc]
    @UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))

而功能：

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
    declare @separator char(1)
    set @separator = ','
    declare @separator_position int
    declare @array_value varchar(MAX)

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0
    begin

        select @separator_position = patindex('%,%' , @array)
        select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as int))
        select @array = stuff(@array, 1, @separator_position, '')
    end
    return
end

这是这个问题的另一个答案。

（新版本发布于 6 年 4 月 13 日）。

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
    {
        var ds = new DataSet();
        using (var sqlConn = new SqlConnection(scsb.ConnectionString))
        {
            var sqlParameters = new List<SqlParameter>();
            var replacementStrings = new Dictionary<string, string>();
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    if (pars[i] is IEnumerable<object>)
                    {
                        List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
                        replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
                        sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
                    }
                    else
                    {
                        sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
                    }
                }
            }
            strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
            using (var sqlCommand = new SqlCommand(strSql, sqlConn))
            {
                if (pars != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                }
                else
                {
                    //Fail-safe, just in case a user intends to pass a single null parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

干杯。

使用动态查询。前端只是生成所需的格式：

DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''

DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)

SQL 小提琴

（编辑：如果表值参数不可用）最好的办法是将大量 IN 参数拆分为多个具有固定长度的查询，因此您有许多已知的 SQL 语句具有固定的参数计数，没有虚拟/重复值，也没有字符串、XML 等解析。

以下是我针对此主题编写的一些 C# 代码：

public static T[][] SplitSqlValues<T>(IEnumerable<T> values)
{
    var sizes = new int[] { 1000, 500, 250, 125, 63, 32, 16, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1 };
    int processed = 0;
    int currSizeIdx = sizes.Length - 1; /* start with last (smallest) */
    var splitLists = new List<T[]>();

    var valuesDistSort = values.Distinct().ToList(); /* remove redundant */
    valuesDistSort.Sort();
    int totalValues = valuesDistSort.Count;

    while (totalValues > sizes[currSizeIdx] && currSizeIdx > 0)
    currSizeIdx--; /* bigger size, by array pos. */

    while (processed < totalValues)
    {
        while (totalValues - processed < sizes[currSizeIdx]) 
            currSizeIdx++; /* smaller size, by array pos. */
        var partList = new T[sizes[currSizeIdx]];
        valuesDistSort.CopyTo(processed, partList, 0, sizes[currSizeIdx]);
        splitLists.Add(partList);
        processed += sizes[currSizeIdx];
    }
    return splitLists.ToArray();
}

（你可能有进一步的想法，省略排序，使用 valuesDistSort.Skip（processed）。Take（size[...]） 而不是列表/数组 CopyTo）。

插入参数变量时，可以创建如下内容：

foreach(int[] partList in splitLists)
{
    /* here: question mark for param variable, use named/numbered params if required */
    string sql = "select * from Items where Id in("
        + string.Join(",", partList.Select(p => "?")) 
        + ")"; /* comma separated ?, one for each partList entry */

    /* create command with sql string, set parameters, execute, merge results */
}

我看过 NHibernate 对象关系映射器生成的 SQL（在查询要从中创建对象的数据时），这在多个查询中看起来最好。在 NHibernate 中，可以指定批处理大小;如果必须提取许多对象数据行，它会尝试检索与批处理大小等效的行数

SELECT * FROM MyTable WHERE Id IN (@p1, @p2, @p3, ... , @p[batch-size])

，而不是发送成百上千

SELECT * FROM MyTable WHERE Id=@id

当剩余的 ID 小于批处理大小，但仍大于 1 个时，它会拆分为更小的语句，但仍具有一定的长度。

如果批处理大小为 100，并且查询包含 118 个参数，则将创建 3 个查询：

• 一个有 100 个参数（batch-size），
• 然后是 12 个
• 另一个有 6 个，

但没有 118 或 18。这样，它将可能的 SQL 语句限制为可能的已知语句，从而防止了太多不同的查询计划，这些计划填满了缓存，并且在很大程度上永远不会被重用。上面的代码执行相同的操作，但长度为 1000、500、250、125、63、32、16、10 比 1。包含超过 1000 个元素的参数列表也会被拆分，从而防止由于大小限制而导致数据库错误。

无论如何，最好有一个直接发送参数化 SQL 的数据库接口，而无需单独的 Prepare 语句和句柄来调用。像 SQL Server 和 Oracle 这样的数据库通过字符串相等来记住 SQL（值会更改，SQL 中的绑定参数不会！），并重用查询计划（如果可用）。无需单独的 prepare 语句，也无需在代码中繁琐地维护查询句柄！ADO.NET 是这样工作的，但似乎 Java 仍然使用按句柄准备/执行（不确定）。

关于这个话题，我有自己的问题，最初建议用重复项填充 IN 子句，但后来更喜欢 NHibernate 样式语句拆分：参数化 SQL - in / not in 具有固定数量的参数，用于查询计划缓存优化？

这个问题仍然很有趣，甚至在被问到 5 年多后......

编辑：我注意到，在给定的情况下，在SQL Server上，具有许多值（如250或更多）的IN查询仍然很慢。虽然我希望数据库在内部创建一种临时表并与之联接，但它似乎只重复了单个值 SELECT 表达式 n 次。每个查询的时间长达约 200 毫秒 - 甚至比将原始 ID 检索 SELECT 与其他相关表联接还要糟糕。此外，SQL Server Profiler 中大约有 10 到 15 个 CPU 单元，这对于重复执行相同的参数化查询来说是不寻常的，这表明在重复调用时创建了新的查询计划。也许像个人查询这样的临时查询一点也不差。我不得不将这些查询与大小变化的非拆分查询进行比较，以得出最终结论，但就目前而言，似乎无论如何都应该避免使用长 IN 子句。

我使用得票最高的答案的更简洁版本：

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();

var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

它确实循环遍历标记参数两次;但这在大多数时候并不重要（这不会是你的瓶颈;如果是，请展开循环）。

如果你真的对性能感兴趣，并且不想遍历循环两次，这里有一个不太漂亮的版本：

var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)  
{
    var paramName = "@tag" + i;

    //Include size and set value explicitly (not AddWithValue)
    //Because SQL Server may use an implicit conversion if it doesn't know
    //the actual size.
    var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; } 
    paramNames.Add(paramName);
    parameters.Add(p);
}

var inClause = string.Join(",", paramNames);

    create FUNCTION [dbo].[ConvertStringToList]


      (@str VARCHAR (MAX), @delimeter CHAR (1))
        RETURNS 
        @result TABLE (
            [ID] INT NULL)
    AS
    BEG

IN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

--您的查询

select * from table where id in ([dbo].[ConvertStringToList(YOUR comma separated string ,',')])

默认情况下，我会通过将表值函数（从字符串返回表）传递给 IN 条件来实现这一点。

这是 UDF 的代码（我从某处的 Stack Overflow 获得它，我现在找不到源代码）

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

一旦你得到了这个，你的代码就会像这样简单：

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

除非你的字符串长得离谱，否则这应该适用于表索引。

如果需要，您可以将其插入到临时表中，为其编制索引，然后运行联接...

在 SQL Server 2016+ 中，另一种可能性是使用 OPENJSON 函数。

这种方法在 OPENJSON 中有所介绍，这是按 ID 列表选择行的最佳方法之一。

下面是一个完整的工作示例

CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
                  FROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESC

GO

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags 

您可以通过执行以下操作以可重用的方式执行此操作 -

public static class SqlWhereInParamBuilder
{
    public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters)
    {
        string[] parameterNames = parameters.Select(
            (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
            .ToArray();

        string inClause = string.Join(",", parameterNames);
        string whereInClause = string.Format(partialClause.Trim(), inClause);

        return whereInClause;
    }

    public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters)
    {
        string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();

        string[] parameterNames = parameterValues.Select(
            (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
            ).ToArray();

        for (int i = 0; i < parameterNames.Length; i++)
        {
            cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
        }
    }
}

有关更多详细信息，请查看此博客文章 - 参数化 SQL WHERE IN 子句 c#

您可以使用STRING_SPLIT函数：SQL Server 2016+

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY [Count] DESC;

艺术

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
  ON t.Name = [value]
ORDER BY [Count] DESC;

现场演示

公认的答案当然会起作用，这是要走的路之一，但它是反模式的。

E. 按值列表查找行

这是对常见反模式的替代，例如在应用程序层或 Transact-SQL 中创建动态 SQL 字符串，或者使用 LIKE 运算符：

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

---

附录：

为了改进表函数行估计，最好将拆分的值具体化为临时表/表变量：STRING_SPLIT

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';

CREATE TABLE #t(val NVARCHAR(120));
INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;

SELECT *
FROM Tags tg
JOIN #t t
  ON t.val = tg.TagName
ORDER BY [Count] DESC;

SEDE - 现场演示

相关新闻： 如何将值列表传递到存储过程中

---

^{原始问题要求SQL Server 2008。因为这个问题经常被用作重复，所以我添加了这个答案作为参考。}

这是 Mark Bracket 的出色答案中解决方案的可重用变体。

扩展方法：

public static class ParameterExtensions
{
    public static Tuple<string, SqlParameter[]> ToParameterTuple<T>(this IEnumerable<T> values)
    {
        var createName = new Func<int, string>(index => "@value" + index.ToString());
        var paramTuples = values.Select((value, index) => 
        new Tuple<string, SqlParameter>(createName(index), new SqlParameter(createName(index), value))).ToArray();
        var inClause = string.Join(",", paramTuples.Select(t => t.Item1));
        var parameters = paramTuples.Select(t => t.Item2).ToArray();
        return new Tuple<string, SqlParameter[]>(inClause, parameters);
    }
}

用法：

        string[] tags = {"ruby", "rails", "scruffy", "rubyonrails"};
        var paramTuple = tags.ToParameterTuple();
        var cmdText = $"SELECT * FROM Tags WHERE Name IN ({paramTuple.Item1})";

        using (var cmd = new SqlCommand(cmdText))
        {
            cmd.Parameters.AddRange(paramTuple.Item2);
        }

有一个很好的、简单的、经过测试的方法可以做到这一点：

/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */

CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY  
AS   
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];

我已经开始使用这种方法来解决我们在实体框架中遇到的问题（对于我们的应用程序来说不够健壮）。因此，我们决定给 Dapper（与 Stack 相同）一个机会。此外，将字符串列表指定为带有 PK 列的表可以大大修改您的执行计划。这是一篇关于如何将表传递到 Dapper 的好文章 - 一切都快速且干净。

创建一个存储名称的临时表，然后使用以下查询：

select * from Tags 
where Name in (select distinct name from temp)
order by Count desc

在 SQL SERVER 2016 或更高版本中，可以使用STRING_SPLIT。

DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT 
    * 
FROM 
    Tags T
    INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY 
    Count DESC

我之所以使用它，是因为有时加入的速度比 Like Operator 在我的查询中工作的速度更快。
此外，您可以将无限数量的输入放入您喜欢的任何分离格式中。
我喜欢这个..

步骤1：-

string[] Ids = new string[] { "3", "6", "14" };
string IdsSP = string.Format("'|{0}|'", string.Join("|", Ids));

步骤2：-

@CurrentShipmentStatusIdArray [nvarchar](255) = NULL

第 3 步：-

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + convert(nvarchar,Shipments.CurrentShipmentStatusId) + '|%'

或

Where @CurrentShipmentStatusIdArray is null or @CurrentShipmentStatusIdArray LIKE '%|' + Shipments.CurrentShipmentStatusId+ '|%'

如果你有兴趣在 SQL 过程和函数中做所有事情（正如最初的问题所表述的那样），而不是在 C# 中连接字符串，你可以使用一个相当简单的方法：

DECLARE @testFilter VARCHAR(50) = 'test1,test2,test3'
SELECT * FROM testTable WHERE testField IN (SELECT value FROM STRING_SPLIT(@testFilter, ','))