我如何参数化一个包含有可变数量参数的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,或者做一些涉及用所需项填充临时表并与临时表连接的事情。
其他回答
我们有一个函数,创建一个表变量,你可以加入:
ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list AS VARCHAR(8000),
@delim AS VARCHAR(10))
RETURNS @listTable TABLE(
Position INT,
Value VARCHAR(8000))
AS
BEGIN
DECLARE @myPos INT
SET @myPos = 1
WHILE Charindex(@delim, @list) > 0
BEGIN
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))
SET @myPos = @myPos + 1
IF Charindex(@delim, @list) = Len(@list)
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,'')
SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
END
IF Len(@list) > 0
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,@list)
RETURN
END
So:
@Name varchar(8000) = null // parameter for search values
select * from Tags
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc
在默认情况下,我将通过向IN条件传递一个表值函数(从字符串返回一个表)来实现这一点。
下面是UDF的代码(我从Stack Overflow的某个地方得到了它,我现在找不到源代码)
CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
一旦你得到了这个,你的代码就会像这样简单:
select * from Tags
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc
除非你有一个长得离谱的字符串,否则这应该与表索引一起工作得很好。
如果需要,你可以把它插入一个临时表,索引它,然后运行一个连接…
在SQL SERVER 2016或更高版本中,您可以使用STRING_SPLIT。
DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT
*
FROM
Tags T
INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY
Count DESC
我使用这个是因为在我的查询中,有时连接速度比Like操作符快。 此外,您还可以按您喜欢的任何单独格式输入无限数量的数据。 我喜欢这个。
我有一个不需要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);
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。