Marcos Crispino posed the following question over at stackoverlow:
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
To which LukeHK posted the following brilliantly simple solution using a TABLE
variable:
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)
References: