Possible Duplicate:
Parameterizing a SQL IN clause?
In SQL Server I'd like to do something to this effect...
DECLARE @Values varchar(1000)
SET @Values = 'A, B, C'
SELECT
blah
FROM
foo
WHERE
myField IN (@Values)
Is this possible or how to accomplish this?
You need a table variable:
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)
in (select …) involved a table scan; adding a primary key to the table variable improved the situation, but not nearly enough. YMMV, of course.Ideally you shouldn't be splitting strings in T-SQL at all.
Barring that change, on older versions before SQL Server 2016, create a split function:
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
Now you can say:
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;
On SQL Server 2016 or above (or Azure SQL Database), it is much simpler and more efficient, however you do have to manually apply LTRIM() to take away any leading spaces:
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;
Use a Temp Table or a Table variable, e.g.
select 'A' as [value]
into #tmp
union
select 'B'
union
select 'C'
and then
SELECT
blah
FROM foo
WHERE myField IN (select [value] from #tmp)
or
SELECT
f.blah
FROM foo f INNER JOIN #tmp t ON f.myField = t.[value]
