where IN clause - 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)

where IN clause

Author

Topic

mike26
Starting Member

5 Posts

Posted - 2010-08-03 : 18:49:38

if I run the following query

SELECT * from sometable
WHERE CITY IN('NY','DALLAS','SAN DIEGO')

it works fine, however, if I wrote it like

SELECT * FROM sometable
WHERE CITY IN(@city)

it doesn't return any data. Any suggestions would greatly be
appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-03 : 19:17:18

http://www.sommarskog.se/arrays-in-sql-2005.html

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-08-05 : 03:25:08

You can done it by creating a string for the whole query

Declare @names nvarchar(50)
Set @names = '''a'', ''b'''
Declare @query nvarchar(500)
Set @query = 'select code from table1
where name in ('+@names+')'

Exec (@query)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-05 : 04:17:42

Beware SQL injection if you use Jomy's code.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-08-05 : 05:08:31

and performance if the IN list is long. Also requires SELECT permission on the underlying table (which is only likely to be an issue if you are using a Stored Procedure)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-08-05 : 15:24:32

Another way would be to use something like the following. It has the added advantage that it can use the index on the column if there is one. Just be careful about commas, leading and trailing spaces etc. in the data

create table #tmp (id int, city varchar(31))

insert into #tmp values (1,'New York');
insert into #tmp values (2,'Denver');
insert into #tmp values (3,'Dallas');
insert into #tmp values (4,'Johannesburg');
insert into #tmp values (5,'Rustenberg');
insert into #tmp values (6,'San Diego');


declare @cities varchar(255);
set @cities = 'Denver,Dallas';

select * from #tmp where 
','+@cities+',' like '%,'+city+',%' 

drop table #tmp

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 02:25:58

"It has the added advantage that it can use the index on the column if there is one"

I'm doubting that looking at your WHERE clause. Some risk from embedded commas in [city] too.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-06 : 11:12:39

quote:
Originally posted by Kristen

"It has the added advantage that it can use the index on the column if there is one"

I'm doubting that looking at your WHERE clause. Some risk from embedded commas in [city] too.

It can USE the index, but it cannot SEEK on the index. So, index use in this case is dubious at best.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources