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

Question

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)

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)