我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
当前回答
我有一个不需要UDF的答案,XML 因为IN接受一个选择语句 例如: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);
其他回答
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
在我看来,正确的方法是将列表存储在字符串中(长度受DBMS支持的限制);唯一的技巧是(为了简化处理)我在字符串的开头和结尾都有一个分隔符(在我的例子中是一个逗号)。其思想是“动态规范化”,将列表转换为单列表,每个值包含一行。这让你可以转弯
In (ct1,ct2, ct3…卡通)
成一个
在(选择…)
或者(我可能更喜欢的解决方案)常规连接,如果你只是添加一个“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”必须返回与的最大行数相同的行数 我们可以在列表中找到的项目)
我使用了一个更简洁的投票结果:
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);
您可以通过以下方法以可重用的方式完成此任务-
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#
这里有另一种选择。只需将一个以逗号分隔的列表作为字符串参数传递给存储过程,然后:
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