Trouble with the IN keyword in transact sql

Question

I am creating a script where I am querying a table using the IN keyword. When I type the data inside the IN clause, the query performs as i should. But when I create a variable with the exact same data in it and use the variable inside the IN clause, it does not. Any Ideas???

Here is the query that works

SELECT * 
FROM scpcommandeventlog 
WHERE MESSAGEid = 3 
  AND PARAM1 IN('11416407','11416410','11416413','11416417',    
                '11416419','11416421','11416423','11416427',
                '11416432','11416433','11416434','11416435',
                '11416438','11416443','11416446','11416448',
                '11416451','11416454','11416458','11416462')

here is the query that doesn't

SELECT * FROM scpcommandeventlog WHERE MESSAGEid = 3 AND PARAM1 IN(@list)

Here is the query that populates the @list variable

DECLARE @List varchar(max)

SELECT @List = isnull(@List + ',', '') + '''' + cast(itemid as varchar) + ''''
FROM dbo.ItemList
WHERE sortid LIKE @sortid

SQL Server won't inspect the contents of the variable to see if it contains a comma delimited list. You are effectively doing `WHERE PARAM1 = '11416407,11416410'...` — Martin Smith, May 04 '13 at 17:27
You need to look into [table-valued parameters](http://msdn.microsoft.com/en-us/library/bb510489(v=sql.100).aspx). — Aaron Bertrand, May 04 '13 at 18:20

score 5 · Answer 1 · answered May 04 '13 at 17:27

You can't put all parameters for an IN query in a single variable, you need a parameter per value as in;

SELECT * 
FROM scpcommandeventlog 
WHERE MESSAGEid = 3 AND PARAM1 IN(@p1,@p2,@p3,...)

You could build the query using dynamic SQL, but a better way in your example may be not to build the list at all and do it all in a single query;

SELECT * 
FROM scpcommandeventlog 
WHERE MESSAGEid = 3 AND PARAM1 IN (
    SELECT itemid 
    FROM dbo.ItemList
    WHERE sortid LIKE @sortid
)

score 1 · Answer 2 · answered May 04 '13 at 17:37

Your IN @list variable is being interpolated as an atomic value and not a list, and so you get no results.

If ItemList is unique with the given parameters, you could replace the IN logic with a simple JOIN:

SELECT g.* 
FROM scpcommandeventlog g
JOIN dbo.ItemList i ON i.itemid = g.PARAM1 AND i.sortid LIKE @sortid
WHERE g.MESSAGEid = 3;

Trouble with the IN keyword in transact sql

2 Answers2