我如何参数化一个包含有可变数量参数的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的优雅方式,我愿意接受。
当前回答
对于这样数量可变的参数,我所知道的唯一方法是显式地生成SQL,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
其他回答
使用动态查询。前端只生成所需的格式:
DECLARE @invalue VARCHAR(100)
SELECT @invalue = '''Bishnu'',''Gautam'''
DECLARE @dynamicSQL VARCHAR(MAX)
SELECT @dynamicSQL = 'SELECT * FROM #temp WHERE [name] IN (' + @invalue + ')'
EXEC (@dynamicSQL)
SQL小提琴
这可能是一种有点讨厌的方法,我用过一次,相当有效。
根据你的目标,它可能会有用。
创建一个只有一列的临时表。 将每个查找值插入到该列中。 不使用IN,只需使用标准JOIN规则。(灵活性++)
这为您所能做的事情提供了一些额外的灵活性,但它更适合这样的情况:需要查询一个大型表,有良好的索引,并且希望多次使用参数化列表。节省了执行两次,所有的卫生工作都是手动完成的。
我从来没有时间去分析它到底有多快,但在我的情况下,它是需要的。
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
在我看来,解决这个问题的最佳来源是这个网站上发布的内容:
Syscomments。Dinakar Nethi
CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS
BEGIN
--DECLARE @T Table (col1 varchar(50))
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @array = @array + @separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- This is where you process the values passed.
INSERT into @T VALUES (@array_value)
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END
Use:
SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')
致谢:Dinakar Nethi
我有一个不需要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);