可能重复:
Parameterizing a SQL IN clause?
在SQL Server中,我想做一些这样的事情...
DECLARE @Values varchar(1000)
SET @Values = 'A, B, C'
SELECT
blah
FROM
foo
WHERE
myField IN (@Values)
这是可能的吗?或者如何实现这一点?
发布于 2011-09-15 21:10:33
您需要一个表变量:
declare @values table
(
Value varchar(1000)
)
insert into @values values ('A')
insert into @values values ('B')
insert into @values values ('C')
select blah
from foo
where myField in (select value from @values)
发布于 2011-09-15 21:14:18
理想情况下,您可以使用shouldn't be splitting strings in T-SQL at all。
除非有此更改,否则在SQL Server 2016之前的旧版本上,请创建一个拆分函数:
CREATE FUNCTION dbo.SplitStrings
(
@List nvarchar(max),
@Delimiter nvarchar(2)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( WITH x(x) AS
(
SELECT CONVERT(xml, N'<root><i>'
+ REPLACE(@List, @Delimiter, N'</i><i>')
+ N'</i></root>')
)
SELECT Item = LTRIM(RTRIM(i.i.value(N'.',N'nvarchar(max)')))
FROM x CROSS APPLY x.nodes(N'//root/i') AS i(i)
);
GO
现在你可以说:
DECLARE @Values varchar(1000);
SET @Values = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN dbo.SplitStrings(@Values, ',') AS s
ON s.Item = foo.myField;
在SQL Server 2016或更高版本(或Azure SQL Database)上,它是much simpler and more efficient,但是您必须手动应用LTRIM()
来删除任何前导空格:
DECLARE @Values varchar(1000) = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN STRING_SPLIT(@Values, ',') AS s
ON LTRIM(s.value) = foo.myField;
发布于 2011-09-15 21:09:58
使用临时表或表变量,例如
select 'A' as [value]
into #tmp
union
select 'B'
union
select 'C'
然后
SELECT
blah
FROM foo
WHERE myField IN (select [value] from #tmp)
或
SELECT
f.blah
FROM foo f INNER JOIN #tmp t ON f.myField = t.[value]
https://stackoverflow.com/questions/7431341
复制相似问题