我如何参数化一个包含有可变数量参数的IN子句的查询，就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中，参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML)，但如果有一些特定于SQL Server 2008的优雅方式，我愿意接受。
当前回答
您可以通过以下方法以可重用的方式完成此任务-
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#
其他回答
对于这样数量可变的参数，我所知道的唯一方法是显式地生成SQL，或者做一些涉及用所需项填充临时表并与临时表连接的事情。
我听到Jeff/Joel今天在播客上谈论这个(第34集，2008-12-16 (MP3, 31 MB)， 1小时03分38秒-1小时06分45秒)，我想我记得Stack Overflow使用LINQ到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已经足够向后看了，但是Contains子句对我来说似乎太向后看了。当我不得不在工作中为一个项目做类似的查询时，我自然地试图用错误的方式来做这个，在本地数组和SQL Server表之间做一个连接，认为LINQ to SQL翻译器将足够聪明，以某种方式处理翻译。它没有这样做，但它确实提供了一个描述性的错误消息，并指示我使用Contains。
无论如何，如果您在强烈推荐的LINQPad中运行此命令，并运行此查询，您可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个参数化为IN子句的值。
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组，并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
这里有另一种选择。只需将一个以逗号分隔的列表作为字符串参数传递给存储过程，然后:
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
另一种可能的解决方案是，不向存储过程传递可变数量的参数，而是传递一个包含您要查找的名称的字符串，但通过将它们围绕在'<>'中使它们唯一。然后使用PATINDEX查找名称:
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0
