select in - SQL Server Forums

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)

select in

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-21 : 05:30:25

the following works

select * from users where sitecode in (256.122)

but this doesn't

declare @site nvarchar(500)
select @site='256,122'
select * from users where sitecode in(@site)

why not?

what i'm trying to accomplish is that I want to pass a variable with a comma delimited list of sitecode

sitecode is one of the fields on the users table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-21 : 05:52:01

you cant pass a variable to in. for achieveing this you can do either of below

1. use a parse function to parse the comma seperated list and make it table of values and join to it like below


declare @site nvarchar(500)
select @site='256,122'

SELECT u.*
FROM users u
INNER JOIN dbo.ParseString(@site,',') f
ON f.Val= u.sitecode

ParseString can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

2. Another way is to regard it as string and do a string comparison using like as below


declare @site nvarchar(500)
select @site='256,122'
select * from users where ',' + @site + ',' LIKE '%,' + cast(sitecode as varchar(10)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources