Passing vars to an in statment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing vars to an in statment

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2008-05-09 : 07:00:24

i am going mad. when i hardcode my usernames into my in statment i get the results i expect...

Code------------------------------
Select u.UserID from dbo.[User] u where u.UserName in ('iresponse\jamie.seaton','iresponse\phil.smith)

Result---------------------------
3509
3506

but when i use a variable with more than one user in it and pass that into my in clause i get nothing returned when i should have the same result set.

Code------------------------------
DECLARE @CollectorList VarChar(100)

Set @CollectorList= '''iresponse\jamie.seaton'',''iresponse\phil.smith'''

Select u.UserID from dbo.[User] u where u.UserName in (@CollectorList)

Result---------------------------
nothing

as i said if i use - Set @CollectorList= 'iresponse\jamie.seaton' - it works fine but when i include another user - Set @CollectorList= '''iresponse\jamie.seaton'',''iresponse\phil.smith''' - i get nothing back.

any ideas guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:08:16

Can't see the pictures.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:30:47

Make use of dynamic SQL.

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-05-09 : 08:00:49

No matter what your @Collectorlist looks like, there's always just one element between the brackets Username in(), right? And that element has to match exactly.

slihp
Yak Posting Veteran

61 Posts

Posted - 2008-05-09 : 08:03:00

id did look at dynamic sql but didnt think it would make much difference as - Set CollectorList= '''iresponse\jamie.seaton'',''iresponse\phil.smith''' - outputs to 'iresponse\jamie.seaton','iresponse\phil.smith' anyway which is the the string i want for the in clause, or am i missing somthing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 08:06:47

Yes, you missed to read the link I provided.
It explains everything.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 08:07:32

[code]Declare @sql varchar(8000)

set @sql = 'Select u.UserID from dbo.[User] u where u.UserName in (' + @CollectorList + ')'
exec(@sql)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

slihp
Yak Posting Veteran

61 Posts

Posted - 2008-05-09 : 08:12:33

from the link peso provided it leads to a best solution which is to unpack the list into a table and then link to that table. Thanks all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources