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 55 · Accepted Answer · answered Sep 15 '11 at 13:10

55

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

34,880
14
126
178

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
4

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

5

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;

edited Jun 07 '19 at 16:04

answered Sep 15 '11 at 13:14

Aaron Bertrand

261,961
36
448
471

When i tried this it only returned the First value in @values. For example, if i wrote Set Values = 'A, B, C'; this only returned A, if i re-arranged it to Set Values = 'B, A, C' it would only give me back B.... – Jun 06 '19 at 21:25
1

@Pr0x1mo Sounds like you declared `varchar` somewhere without specifying a length? Can you show a repro somewhere? Because this solution works for me as posted. – Aaron Bertrand Jun 06 '19 at 21:47
1

use CLSInc declare User VARCHAR(1000); SET User = 'ABBY SIMON, AMY SALAS'; SELECT [whoami] ,[TRCK_DATE] , ([STIME]) as 'Start time' ,row_number() over(partition by whoami, trck_date order by stime) as 'row check' -- into #stime FROM [CLSInc].[dbo].[TRACKUSR] inner join dbo.SplitStrings(@User, ',') as s on s.item = whoami where trck_date between '05/01/2019' and '05/02/2019' group by whoami, trck_date, stime order by STIME – Jun 07 '19 at 12:58
1

@Pr0x1mo Try `ON LTRIM(s.item) = whoami` or `ON s.item = ' ' + whoami`. You can also change `RTRIM(...)` in the function to `LTRIM(RTRIM(...))`. You can use `RIGHT OUTER JOIN` temporarily to prove that there are multiple rows coming out of the function. Your original complaint suggested that the function was only returning one row; now it's clear they're getting filtered out by the join. – Aaron Bertrand Jun 07 '19 at 14:04
oh shit that worked. Thanks a lot! – Jun 07 '19 at 19:05

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