Parameterizing a SQL ‘IN’ Clause

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:

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

Leave a Reply

Your email address will not be published. Required fields are marked *