参数化 SQL IN 子句

Parameterize an SQL IN clause

提问by Jeff Atwood

How do I parameterize a query containing an INclause with a variable number of arguments, like this one?

如何参数化包含IN具有可变数量参数的子句的查询，例如这个？

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

In this query, the number of arguments could be anywhere from 1 to 5.

在这个查询中，参数的数量可以是 1 到 5 之间的任何地方。

I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to SQL Server 2008, I am open to that.

我不想为此（或 XML）使用专用的存储过程，但如果有一些特定于SQL Server 2008 的优雅方式，我对此持开放态度。

采纳答案by Joel Spolsky

Here's a quick-and-dirty technique I have used:

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

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

So here's the C# code:

所以这是 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);
}

Two caveats:

两个注意事项：

• The performance is terrible. LIKE "%...%"queries are not indexed.
• Make sure you don't have any |, blank, or null tags or this won't work

• 表演太可怕了。LIKE "%...%"查询未编入索引。
• 确保您没有任何|、空白或空标签，否则这将不起作用

There are other ways to accomplish this that some people may consider cleaner, so please keep reading.

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

回答by Mark Brackett

You can parameterize eachvalue, so something like:

您可以参数化每个值，例如：

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]);
    }
}

Which will give you:

这会给你：

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"

No, this is not open to SQL injection. The only injected text into CommandText is not based on user input. It's solely based on the hardcoded "@tag" prefix, and the index of an array. The index will alwaysbe an integer, is not user generated, and is safe.

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

The user inputted values are still stuffed into parameters, so there is no vulnerability there.

用户输入的值仍然被塞进参数中，所以那里没有漏洞。

Edit:

编辑：

Injection concerns aside, take care to note that constructing the command text to accomodate a variable number of parameters (as above) impede's SQL server's ability to take advantage of cached queries. The net result is that you almost certainly lose the value of using parameters in the first place (as opposed to merely inserting the predicate strings into the SQL itself).

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

Not that cached query plans aren't valuable, but IMO this query isn't nearly complicated enough to see much benefit from it. While the compilation costs may approach (or even exceed) the execution costs, you're still talking milliseconds.

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

If you have enough RAM, I'd expect SQL Server would probably cache a plan for the common counts of parameters as well. I suppose you could always add five parameters, and let the unspecified tags be NULL - the query plan should be the same, but it seems pretty ugly to me and I'm not sure that it'd worth the micro-optimization (although, on Stack Overflow - it may very well be worth it).

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

Also, SQL Server 7 and later will auto-parameterize queries, so using parameters isn't really necessary from a performance standpoint - it is, however, criticalfrom a security standpoint - especially with user inputted data like this.

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

回答by Mark Brackett

For SQL Server 2008, you can use a table valued parameter. It's a bit of work, but it is arguably cleaner than my other method.

对于 SQL Server 2008，您可以使用表值参数。这有点工作，但可以说比我的其他方法更干净。

First, you have to create a type

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

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

Then, your ADO.NET code looks like this:

然后，您的 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 = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

回答by spencer7593

The original question was "How do I parameterize a query ..."

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

Let me state right here, that this is not an answerto the original question. There are already some demonstrations of that in other good answers.

让我在此声明，这不是原始问题的答案。在其他好的答案中已经有一些示范。

With that said, go ahead and flag this answer, downvote it, mark it as not an answer... do whatever you believe is right.

话虽如此，继续标记这个答案，否决它，将其标记为不是答案......做任何你认为正确的事情。

See the answer from Mark Brackett for the preferred answer that I (and 231 others) upvoted. The approach given in his answer allows 1) for effective use of bind variables, and 2) for predicates that are sargable.

请参阅 Mark Brackett 的回答，了解我（和其他 231 人）赞成的首选答案。他的回答中给出的方法允许 1) 有效使用绑定变量，以及 2) 可用于谓词的谓词。

Selected answer

选择答案

What I want to address here is the approach given in Joel Spolsky's answer, the answer "selected" as the right answer.

我想在这里解决的是乔尔·斯波尔斯基 (Joel Spolsky) 的回答中给出的方法，即“选择”作为正确答案的答案。

Joel Spolsky's approach is clever. And it works reasonably, it's going to exhibit predictable behavior and predictable performance, given "normal" values, and with the normative edge cases, such as NULL and the empty string. And it may be sufficient for a particular application.

Joel Spolsky 的方法很聪明。它运行合理，在给定“正常”值和规范的边缘情况（例如 NULL 和空字符串）的情况下，它将表现出可预测的行为和可预测的性能。并且对于特定的应用程序可能就足够了。

But in terms generalizing this approach, let's also consider the more obscure corner cases, like when the Namecolumn contains a wildcard character (as recognized by the LIKE predicate.) The wildcard character I see most commonly used is %(a percent sign.). So let's deal with that here now, and later go on to other cases.

但在概括这种方法时，让我们也考虑更模糊的极端情况，例如当Name列包含通配符时（由 LIKE 谓词识别）。我看到最常用的通配符是%（百分号。）。所以现在让我们在这里处理这个问题，然后再讨论其他情况。

Some problems with % character

% 字符的一些问题

Consider a Name value of 'pe%ter'. (For the examples here, I use a literal string value in place of the column name.) A row with a Name value of `'pe%ter' would be returned by a query of the form:

考虑一个 Name 值'pe%ter'。（对于此处的示例，我使用文字字符串值代替列名。）名称值为“pe%ter”的行将通过以下形式的查询返回：

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

But that same row will notbe returned if the order of the search terms is reversed:

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

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

The behavior we observe is kind of odd. Changing the order of the search terms in the list changes the result set.

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

It almost goes without saying that we might not want pe%terto match peanut butter, no matter how much he likes it.

几乎不用说，我们可能不想pe%ter搭配花生酱，无论他多么喜欢它。

Obscure corner case

暗角案例

(Yes, I will agree that this is an obscure case. Probably one that is not likely to be tested. We wouldn't expect a wildcard in a column value. We may assume that the application prevents such a value from being stored. But in my experience, I've rarely seen a database constraint that specifically disallowed characters or patterns that would be considered wildcards on the right side of a LIKEcomparison operator.

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

Patching a hole

修补一个洞

One approach to patching this hole is to escape the %wildcard character. (For anyone not familiar with the escape clause on the operator, here's a link to the SQL Server documentation.

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

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

Now we can match the literal %. Of course, when we have a column name, we're going to need to dynamically escape the wildcard. We can use the REPLACEfunction to find occurrences of the %character and insert a backslash character in front of each one, like this:

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

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

So that solves the problem with the % wildcard. Almost.

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

Escape the escape

逃脱逃脱

We recognize that our solution has introduced another problem. The escape character. We see that we're also going to need to escape any occurrences of escape character itself. This time, we use the ! as the escape character:

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

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

The underscore too

下划线也是

Now that we're on a roll, we can add another REPLACEhandle the underscore wildcard. And just for fun, this time, we'll use $ as the escape character.

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

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

I prefer this approach to escaping because it works in Oracle and MySQL as well as SQL Server. (I usually use the \ backslash as the escape character, since that's the character we use in regular expressions. But why be constrained by convention!

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

Those pesky brackets

那些讨厌的括号

SQL Server also allows for wildcard characters to be treated as literals by enclosing them in brackets []. So we're not done fixing yet, at least for SQL Server. Since pairs of brackets have special meaning, we'll need to escape those as well. If we manage to properly escape the brackets, then at least we won't have to bother with the hyphen -and the carat ^within the brackets. And we can leave any %and _characters inside the brackets escaped, since we'll have basically disabled the special meaning of the brackets.

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

Finding matching pairs of brackets shouldn't be that hard. It's a little more difficult than handling the occurrences of singleton % and _. (Note that it's not sufficient to just escape all occurrences of brackets, because a singleton bracket is considered to be a literal, and doesn't need to be escaped. The logic is getting a little fuzzier than I can handle without running more test cases.)

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

Inline expression gets messy

内联表达式变得混乱

That inline expression in the SQL is getting longer and uglier. We can probably make it work, but heaven help the poor soul that comes behind and has to decipher it. As much of a fan I am for inline expressions, I'm inclined not use one here, mainly because I don't want to have to leave a comment explaining the reason for the mess, and apologizing for it.

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

A function where ?

一个函数在哪里？

Okay, so, if we don't handle that as an inline expression in the SQL, the closest alternative we have is a user defined function. And we know that won't speed things up any (unless we can define an index on it, like we could with Oracle.) If we've got to create a function, we might better do that in the code that calls the SQL statement.

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

And that function may have some differences in behavior, dependent on the DBMS and version. (A shout out to all you Java developers so keen on being able to use any database engine interchangeably.)

并且该函数在行为上可能有一些差异，这取决于 DBMS 和版本。（向所有热衷于能够互换使用任何数据库引擎的 Java 开发人员大喊一声。）

Domain knowledge

领域知识

We may have specialized knowledge of the domain for the column, (that is, the set of allowable values enforced for the column. We may know a priorithat the values stored in the column will never contain a percent sign, an underscore, or bracket pairs. In that case, we just include a quick comment that those cases are covered.

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

The values stored in the column may allow for % or _ characters, but a constraint may require those values to be escaped, perhaps using a defined character, such that the values are LIKE comparison "safe". Again, a quick comment about the allowed set of values, and in particular which character is used as an escape character, and go with Joel Spolsky's approach.

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

But, absent the specialized knowledge and a guarantee, it's important for us to at least consider handling those obscure corner cases, and consider whether the behavior is reasonable and "per the specification".

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

---

---

Other issues recapitulated

其他问题重述

I believe others have already sufficiently pointed out some of the other commonly considered areas of concern:

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

• SQL injection(taking what would appear to be user supplied information, and including that in the SQL text rather than supplying them through bind variables. Using bind variables isn't required, it's just one convenient approach to thwart with SQL injection. There are other ways to deal with it:

• optimizer plan using index scan rather than index seeks, possible need for an expression or function for escaping wildcards (possible index on expression or function)

• using literal values in place of bind variables impacts scalability

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

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

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

---

---

Conclusion

结论

I like Joel Spolsky's approach. It's clever. And it works.

我喜欢乔尔·斯波尔斯基的方法。它很聪明。它有效。

But as soon as I saw it, I immediately saw a potential problem with it, and it's not my nature to let it slide. I don't mean to be critical of the efforts of others. I know many developers take their work very personally, because they invest so much into it and they care so much about it. So please understand, this is not a personal attack. What I'm identifying here is the type of problem that crops up in production rather than testing.

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

Yes, I've gone far afield from the original question. But where else to leave this note concerning what I consider to be an important issue with the "selected" answer for a question?

是的，我已经离最初的问题很远了。但是，关于我认为是问题的“选定”答案的重要问题，还有什么地方可以留下这个注释呢？

回答by David Basarab

You can pass the parameter as a string

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

So you have the string

所以你有字符串

DECLARE @tags

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

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

Then all you have to do is pass the string as 1 parameter.

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

Here is the split function I use.

这是我使用的拆分功能。

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

回答by Peter Meyer

I heard Jeff/Joel talk about this on the podcast today (episode 34, 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 secs - 1 h 06 min 45 secs), and I thought I recalled Stack Overflow was using LINQ to SQL, but maybe it was ditched. Here's the same thing in LINQ to SQL.

我今天在播客上听到杰夫/乔尔谈论这个（第 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;

That's it. And, yes, LINQ already looks backwards enough, but the Containsclause seems extra backwards to me. When I had to do a similar query for a project at work, I naturally tried to do this the wrong way by doing a join between the local array and the SQL Server table, figuring the LINQ to SQL translator would be smart enough to handle the translation somehow. It didn't, but it did provide an error message that was descriptive and pointed me towards using Contains.

就是这样。而且，是的，LINQ 已经足够向后看，但Contains对我来说，该子句似乎更加向后。当我不得不对工作中的项目进行类似的查询时，我很自然地尝试通过在本地数组和 SQL Server 表之间进行连接来以错误的方式执行此操作，认为 LINQ to SQL 转换器将足够智能以处理不知何故翻译。它没有，但它确实提供了一个描述性的错误消息，并指出我使用Contains。

Anyway, if you run this in the highly recommended LINQPad, and run this query, you can view the actual SQL that the SQL LINQ provider generated. It'll show you each of the values getting parameterized into an INclause.

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

回答by Marc Gravell

If you are calling from .NET, you could use Dapper dot net:

如果您从 .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});

Here Dapper does the thinking, so you don't have to. Something similar is possible with LINQ to SQL, of course:

在这里，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;

回答by Kent Fredric

This is possibly a half nasty way of doing it, I used it once, was rather effective.

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

Depending on your goals it might be of use.

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

1. Create a temp tablewith one column.
2. INSERTeach look-up value into that column.
3. Instead of using an IN, you can then just use your standard JOINrules. ( Flexibility++ )

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

This has a bit of added flexibility in what you can do, but it's more suited for situations where you have a large table to query, with good indexing, and you want to use the parametrized list more than once. Saves having to execute it twice and have all the sanitation done manually.

这在你可以做的事情上有一点额外的灵活性，但它更适合你有一个大表要查询的情况，有良好的索引，并且你想不止一次使用参数化列表。无需执行两次并手动完成所有卫生工作。

I never got around to profiling exactly how fastit was, but in my situation it was needed.

我从来没有开始分析它到底有多快，但在我的情况下是需要的。

回答by Lukasz Szozda

In SQL Server 2016+you could use STRING_SPLITfunction:

在SQL Server 2016+你可以使用STRING_SPLIT功能：

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

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

or:

或者：

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;

LiveDemo

现场演示

The accepted answerwill of course work and it is one of the way to go, but it is anti-pattern.

该接受的答案当然工作的意愿，这是要走的路，但它是反模式。

E. Find rows by list of values

This is replacement for common anti-pattern such as creating a dynamic SQL string in application layer or Transact-SQL, or by using LIKE operator:

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

E. 按值列表查找行

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

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

---

---

Addendum:

附录：

To improve the STRING_SPLITtable function row estimation, it is a good idea to materialize splitted values as temporary table/table variable:

为了改进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 - Live Demo

SEDE - 现场演示

Related: How to Pass a List of Values Into a Stored Procedure

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

---

---

^{Original question has requirement SQL Server 2008. Because this question is often used as duplicate, I've added this answer as reference. 原题有要求SQL Server 2008。因为这个问题经常被重复使用，所以我添加了这个答案作为参考。}

回答by David Robbins

We have function that creates a table variable that you can join to:

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

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 

So:

所以：

@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