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

Question

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

Answer 1

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

Have a look at sp_ExecuteSQL

Answer 2

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

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.

3.

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)

Answer 3

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)

Answer 4

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.

Answer 5

This question was asked before and nicely covered by Joel

http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause

With an amazingly good solution from Mark