我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
下面是我用过的一个快速而又复杂的技巧:
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);
}
两个问题:
演出糟透了。像“%……%"查询没有索引。 请确保没有任何|、blank或null标记,否则将无法工作
有些人可能认为还有其他更清洁的方法可以做到这一点,所以请继续阅读。
其他回答
这里有另一种选择。只需将一个以逗号分隔的列表作为字符串参数传递给存储过程,然后:
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
可以将参数作为字符串传递
这是弦
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
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 ,',')])
有一个很好的、简单的、经过测试的方法:
/* 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的好文章——都是快速而干净的。
这是一个可重复使用的变化的解决方案在马克托罗特的优秀答案。
扩展方法:
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);
}