Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble with the IN keyword Expand / Collapse
Author
Message
Posted Saturday, May 04, 2013 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:14 AM
Points: 1, Visits: 9
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
Post #1449446
Posted Saturday, May 04, 2013 1:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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)
Post #1449453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse