Something wrong with in keyword

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)

Something wrong with in keyword

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-12-03 : 07:45:36

Hello guys,
am having a problem getting this to work.

Whats wrong using the in keyword here



declare @csv varchar(max), @groups varchar(max)

set @groups = 'singles,married'

Select @csv = isnull(@csv + ',', '') + Number
From sms_PhoneNumber_Groups_Numbers
Where Group_Name in (@groups) and Username = 'hot'

print @csv

print @csv is returning null. Whats wrong with the in keyword ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-03 : 07:49:55

well the in doesn't work like that

you have to split the csv into a table and join to it.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 07:58:33

You can search for ParseValue in this forum.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 08:51:54

or try

declare @csv varchar(max), @groups varchar(max)

set @groups = 'singles,married'

Select @csv = isnull(@csv + ',', '') + Number
From sms_PhoneNumber_Groups_Numbers
Where ','+@groups+',' like '%,'+Group_Name+',%' and Username = 'hot'

print @csv

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-12-03 : 09:06:11

quote:
Originally posted by madhivanan

or try

declare @csv varchar(max), @groups varchar(max)

set @groups = 'singles,married'

Select @csv = isnull(@csv + ',', '') + Number
From sms_PhoneNumber_Groups_Numbers
Where ','+@groups+',' like '%,'+Group_Name+',%' and Username = 'hot'

print @csv

Madhivanan

Failing to plan is Planning to fail

Quite Impressive Madhi, it works.

though Am still trying to read up more on the IN KEYWORD or using a split function.

Thanks once again
Ehi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 09:19:36

IN (@var) is not possible directly until you use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 09:21:25

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111986

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-12-03 : 09:47:43

quote:
Originally posted by madhivanan

IN (@var) is not possible directly until you use Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Ok thanks. But it does work with a split function.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-12-03 : 09:49:50

quote:
Originally posted by visakh16

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111986

Thanks Visakh, Spirit1, But i dont want to return a table.

Madhi, example works, as I want to use it as a CSV value. But brilliant example

Ehi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 10:15:55

this is what Mladen & i meant


declare @csv varchar(max), @groups varchar(max)

set @groups = 'singles,married'

Select @csv = isnull(@csv + ',', '') + n.Number
From sms_PhoneNumber_Groups_Numbers n
INNER JOIN (SELECT Val FROM dbo.ParseValues(@groups,','))f
ON f.Val=n.Group_Name
Where  n.Username = 'hot'

print @csv

parsevalues function is in my last posted link

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-12-03 : 12:53:10

quote:

Originally posted by visakh16

this is what Mladen & i meant


declare @csv varchar(max), @groups varchar(max)

set @groups = 'singles,married'

Select @csv = isnull(@csv + ',', '') + n.Number
From sms_PhoneNumber_Groups_Numbers n
INNER JOIN (SELECT Val FROM dbo.ParseValues(@groups,','))f
ON f.Val=n.Group_Name
Where  n.Username = 'hot'

print @csv

parsevalues function is in my last posted link

Oh

Msg 208, Level 16, State 1, Line 5
Invalid object name 'dbo.ParseValues'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:18:53

did you create parsevalues as per my earlier link? ALso is your default schema dbo?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources