Set variable with multiple values and use IN

Question

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?

If this is passed as a param see http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor & http://www.sommarskog.se/arrays-in-sql.html — Alex K., Sep 15 '11 at 13:12

score 60 · Accepted Answer · answered Sep 15 '11 at 13:10

60

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)

answered Sep 15 '11 at 13:10

Christian Specht

35,843
15
128
182

2

FYI, in SQL Server 2008 and above, the following syntax is also valid for inserting multiple explicit values into a table variable: `insert into @values values ('A'),('B'),('C') – Chiramisu Jun 21 '13 at 23:36
5

Cautionary anecdote: I recently did something like this to make a query more maintainable… and quickly reversed course after seeing a nearly **2000%** increase in execution time, from under ~800ms to over 16,000ms. A cursory investigation revealed that each `in (select …)` involved a table scan; adding a primary key to the table variable improved the situation, but not nearly enough. YMMV, of course. – Jordan Gray Oct 02 '13 at 09:16
4

SQL Server 2016 or later you could use String Split function DECLARE @Values varchar(1000) = 'A,B,C,D,E' SELECT * FROM WHERE ColumnName IN (SELECT VALUE FROM STRING_SPLIT(@Values,',')) – SimonOzturk Feb 21 '18 at 11:46

Aaron Bertrand · Answer 2 · 2019-06-07T16:04:25.843

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;

score 1 · Answer 3 · answered Sep 15 '11 at 13:09

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]

Set variable with multiple values and use IN

3 Answers3

Linked

Related