Passing comma delimited value to IN clause

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 2012 Forums

Transact-SQL (2012)

Passing comma delimited value to IN clause

Author

Topic

NewSQL11
Starting Member

6 Posts

Posted - 2014-08-23 : 16:23:09

Running into problems when I pass -

MyVariable = 'One','Two','Three'

to -


select a.system, count(b.[Name]) SystemCount
from [System] a
left join NameIndustry] b
on a.system = b.system
where  b.industry in ([MyVariable])
group by a.system
order by a.system

Works as designed if 1 value but the multi value returns nothing except the standard expression of non-Boolean type near comma.

I have read a lot on possible solutions but having a hard time following them as I want to understand the meaning behind a solution and see if with the expansion of technology if the possible outcomes have shifted.

Any help would be appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-24 : 08:44:41

In standard SQL, use dynamic SQL. If this is SSRs, there's another easier way

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-24 : 09:33:25

use a CSV parsing function like
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


set @MyVariable = 'One,Two,Three'

where  b.industry in (select stringval from CSVTable(@MyVariable))

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources