参数化 SQL IN 子句

2021-12-01 00:00:00 sql parameters sql-server

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

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

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

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

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

我不想为此(或 XML)使用专用的存储过程，但如果有一些特定于 SQL Server 2008，我对此持开放态度.

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.

推荐答案

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

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

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 "%...%" 查询未编入索引.
  • 确保您没有任何 |、空白或空标签，否则这将不起作用
  • 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

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

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

相关文章