Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this:

select * from myTable where myColumn in (1,2,3,4)

Is there a way to define a variable to hold the entire list "(1,2,3,4)"? How should I define it?

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

DECLARE @mylist TABLE (Id int)
INSERT INTO @mylist
SELECT id FROM (VALUES (1),(2),(3),(4),(5)) AS tbl(id)

SELECT * FROM Mytable WHERE theColumn IN (select id from @mylist)

There are two ways to tackle dynamic csv lists for TSQL queries:

1. Using an inner select

   SELECT * FROM myTable WHERE myColumn in (SELECT id FROM myIdTable WHERE id > 10)

2. Using dynamically concatenated TSQL

   DECLARE @sql varchar(max)
   declare @list varchar(256)
   select @list = '1,2,3'
   SELECT @sql = 'SELECT * FROM myTable WHERE myColumn in (' + @list + ')'

   exec sp_executeSQL @sql

3. A possible third option is table variables. If you have SQl Server 2005 you can use a table variable. If your on Sql Server 2008 you can even pass whole table variables in as a parameter to stored procedures and use it in a join or as a subselect in the IN clause.

   DECLARE @list TABLE (Id INT)

   INSERT INTO @list(Id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4

   SELECT * FROM myTable JOIN @list l ON myTable.myColumn = l.Id

   SELECT * FROM myTable WHERE myColumn IN (SELECT Id FROM @list)

Use a function like this:

CREATE function [dbo].[list_to_table] (@list varchar(4000))
returns @tab table (item varchar(100))
begin

if CHARINDEX(',',@list) = 0 or CHARINDEX(',',@list) is null
begin
	insert into @tab (item) values (@list);
	return;
end


declare @c_pos int;
declare @n_pos int;
declare @l_pos int;

set @c_pos = 0;
set @n_pos = CHARINDEX(',',@list,@c_pos);

while @n_pos > 0
begin
	insert into @tab (item) values (SUBSTRING(@list,@c_pos+1,@n_pos - @c_pos-1));
	set @c_pos = @n_pos;
	set @l_pos = @n_pos;
	set @n_pos = CHARINDEX(',',@list,@c_pos+1);
end;

insert into @tab (item) values (SUBSTRING(@list,@l_pos+1,4000));

return;
end;

Instead of using like, you make an inner join with the table returned by the function:

select * from table_1 where id in ('a','b','c')

becomes

select * from table_1 a inner join [dbo].[list_to_table] ('a,b,c') b on (a.id = b.item)

In an unindexed 1M record table the second version took about half the time...

Starting with SQL2017 you can use STRING_SPLIT and do this:

declare @myList nvarchar(MAX)
set @myList = '1,2,3,4'
select * from myTable where myColumn in (select value from STRING_SPLIT(@myList,','))

DECLARE @myList TABLE (Id BIGINT) INSERT INTO @myList(Id) VALUES (1),(2),(3),(4);
select * from myTable where myColumn in(select Id from @myList)

Please note that for long list or production systems it's not recommended to use this way as it may be much more slower than simple INoperator like someColumnName in (1,2,3,4) (tested using 8000+ items list)

I know this is old now but TSQL => 2016, you can use STRING_SPLIT:

DECLARE @InList varchar(255) = 'This;Is;My;List';

WITH InList (Item) AS (
	SELECT value FROM STRING_SPLIT(@InList, ';')
)

SELECT * 
FROM [Table]
WHERE [Item] IN (SELECT Tag FROM InList)

slight improvement on @LukeH, there is no need to repeat the "INSERT INTO": and @realPT's answer - no need to have the SELECT:

DECLARE @MyList TABLE (Value INT) 
INSERT INTO @MyList VALUES (1),(2),(3),(4)

SELECT * FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

No, there is no such type. But there are some choices:

• Dynamically generated queries (sp_executesql)
• Temporary tables
• Table-type variables (closest thing that there is to a list)
• Create an XML string and then convert it to a table with the XML functions (really awkward and roundabout, unless you have an XML to start with)

None of these are really elegant, but that's the best there is.

If you want to do this without using a second table, you can do a LIKE comparison with a CAST:

DECLARE @myList varchar(15)
SET @myList = ',1,2,3,4,'

SELECT *
FROM myTable
WHERE @myList LIKE '%,' + CAST(myColumn AS varchar(15)) + ',%'

If the field you're comparing is already a string then you won't need to CAST.

Surrounding both the column match and each unique value in commas will ensure an exact match. Otherwise, a value of 1 would be found in a list containing ',4,2,15,'

As no one mentioned it before, starting from Sql Server 2016 you can also use json arrays and OPENJSON (Transact-SQL):

declare @filter nvarchar(max) = '[1,2]'

select *
from dbo.Test as t
where
    exists (select * from openjson(@filter) as tt where tt.[value] = t.id)

You can test it in sql fiddle demo

You can also cover more complicated cases with json easier - see Search list of values and range in SQL using WHERE IN clause with SQL variable?

This one uses PATINDEX to match ids from a table to a non-digit delimited integer list.

-- Given a string @myList containing character delimited integers 
-- (supports any non digit delimiter)
DECLARE @myList VARCHAR(MAX) = '1,2,3,4,42'

SELECT * FROM [MyTable]
    WHERE 
        -- When the Id is at the leftmost position 
		-- (nothing to its left and anything to its right after a non digit char) 
        PATINDEX(CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0 
        OR
        -- When the Id is at the rightmost position
		-- (anything to its left before a non digit char and nothing to its right) 
        PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR), @myList)>0
        OR
        -- When the Id is between two delimiters 
		-- (anything to its left and right after two non digit chars)
        PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0
        OR
        -- When the Id is equal to the list
		-- (if there is only one Id in the list)
        CAST([Id] AS VARCHAR)=@myList

Notes:

• when casting as varchar and not specifying byte size in parentheses the default length is 30
• % (wildcard) will match any string of zero or more characters
• ^ (wildcard) not to match
• [^0-9] will match any non digit character
• PATINDEX is an SQL standard function that returns the position of a pattern in a string

DECLARE @StatusList varchar(MAX);
SET @StatusList='1,2,3,4';
DECLARE @Status SYS_INTEGERS;
INSERT INTO  @Status 
SELECT Value 
FROM dbo.SYS_SPLITTOINTEGERS_FN(@StatusList, ',');
SELECT Value From @Status;

Most of these seem to focus on separating-out each INT into its own parenthetical, for example:

• (1),(2),(3), and so on...

That isn't always convenient. Especially since, many times, you already start with a comma-separated list, for example:

• (1,2,3,...) and so on...

In these situations, you may care to do something more like this:

DECLARE @ListOfIds TABLE (DocumentId INT);

INSERT INTO @ListOfIds
SELECT Id FROM [dbo].[Document] WHERE Id IN (206,235,255,257,267,365)

SELECT * FROM @ListOfIds

I like this method because, more often than not, I am trying to work with IDs that should already exist in a table.

I think you'll have to declare a string and then execute that SQL string.

Have a look at sp_executeSQL