我如何参数化一个包含有可变数量参数的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 Server 2016+中,您可以使用STRING_SPLIT函数:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT *
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY [Count] DESC;
or:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
ON t.Name = [value]
ORDER BY [Count] DESC;
现场演示
公认的答案当然是可行的,这是一种方法,但它是反模式的。
E.按值列表查找行 这是对常见反模式的替代,例如在应用层或Transact-SQL中创建动态SQL字符串,或者使用LIKE操作符: 选择ProductId,名称,标签 从产品 ”,1、2、3”,像“%”+投(ProductId VARCHAR(20吗 )) + ',%';
附录:
为了改进STRING_SPLIT表函数行估计,将分离的值实体化为临时表/表变量是一个好主意:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';
CREATE TABLE #t(val NVARCHAR(120));
INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;
SELECT *
FROM Tags tg
JOIN #t t
ON t.val = tg.TagName
ORDER BY [Count] DESC;
现场演示
相关内容:如何将值列表传递给存储过程
原来的问题有要求SQL Server 2008。因为这个问题经常被重复使用,所以我添加了这个答案作为参考。
其他回答
我们有一个函数,创建一个表变量,你可以加入:
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
下面是我用过的一个快速而又复杂的技巧:
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标记,否则将无法工作
有些人可能认为还有其他更清洁的方法可以做到这一点,所以请继续阅读。
如果在IN子句中存储了以逗号(,)分隔的字符串,则可以使用charindex函数获取值。如果使用. net,则可以使用SqlParameters进行映射。
DDL Script:
CREATE TABLE Tags
([ID] int, [Name] varchar(20))
;
INSERT INTO Tags
([ID], [Name])
VALUES
(1, 'ruby'),
(2, 'rails'),
(3, 'scruffy'),
(4, 'rubyonrails')
;
t - sql:
DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
您可以在. net代码中使用上述语句并将参数映射为SqlParameter。
提琴手演示
编辑: 使用下面的脚本创建名为SelectedTags的表。
DDL Script:
Create table SelectedTags
(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')
t - sql:
DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
可以将参数作为字符串传递
这是弦
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