SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Trouble with "IN"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dave.bodenheimer
Starting Member

USA
5 Posts

Posted - 05/04/2013 :  13:08:20  Show Profile  Reply with Quote
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

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/04/2013 :  13:56:39  Show Profile  Visit chadmat's Homepage  Reply with Quote
Because you can't do that. You would need to create the entire query as a string and execute it.

-Chad
Go to Top of Page

dave.bodenheimer
Starting Member

USA
5 Posts

Posted - 05/04/2013 :  14:10:38  Show Profile  Reply with Quote
Thanks Chad, while waiting for an answer i did that and it works great.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/06/2013 :  02:09:16  Show Profile  Reply with Quote
you can do this though but it may not perform well for large datasets


SELECT * FROM scpcommandeventlog WHERE MESSAGEid = 3 AND ',' + @list + ',' LIKE '%,' + CAST(PARAM1  AS varchar(10)) + ',%'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1673 Posts

Posted - 05/10/2013 :  18:05:42  Show Profile  Reply with Quote
You could also split the data in your variable and return it as a table as part of your IN clause. There are a variety of split functions to be found but the best I'[ve run across is Jeff Moden's. It can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

HTH

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Edited by - Bustaz Kool on 05/10/2013 18:06:21
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000