Trouble with the IN keyword

• Dave Bodenheimer-467684

  SSC Rookie

  Points: 41

  More actions

  May 4, 2013 at 11:17 am

  #273552

  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

• geoff5

  Hall of Fame

  Points: 3560

  More actions

  May 4, 2013 at 1:06 pm

  #1612410

  Rather than populate a variable with a subquery, use a simpler subquery directly inside the IN value list position, like this:

  select * from scpcommandeventlog where messageid = 3 and param1 in (select itemid from dbo.ItemList where sortid like @sortid)