How to assign multiple values to variable

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)

How to assign multiple values to variable

Author

Topic

drew22299
Starting Member

26 Posts

Posted - 2009-01-23 : 11:05:46

Hi,

I have a variable that is declared and has the value set, and a where clause that uses the value assigned to the variable but I want to use the IN operator

DECLARE @PeriodUID nvarchar(25)
SET @PeriodUID = '969'

where Client.ClientUID = @ClientUID and PeriodBrief.PeriodBriefUID IN (@PeriodUID)

How do I assign multiple values to the @PeriodUID? I get an error when I try SET @PeriodUID = '969','342','432'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:07:12

you cant. a variable can hold only a single value. you need to use table variable if you want to store multiple values

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:11:16

I think you need to modify your where clause as below

where Client.ClientUID = @ClientUID and
','+@PeriodUID+',' like '%,'+PeriodBrief.PeriodBriefUID+',%'

and change @PeriodUID = '969','342','432' statement as

SET @PeriodUID = '969,342,432'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:14:53

quote:
Originally posted by raky

I think you need to modify your where clause as below

where Client.ClientUID = @ClientUID and
','+@PeriodUID+',' like '%,'+CAST(PeriodBrief.PeriodBriefUID AS varchar(20))+',%'

and change @PeriodUID = '969','342','432' statement as

SET @PeriodUID = '969,342,432'

if PeriodBrief.PeriodBriefUID is int modify as above

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:17:06

quote:
Originally posted by visakh16

quote:
Originally posted by raky

I think you need to modify your where clause as below

where Client.ClientUID = @ClientUID and
','+@PeriodUID+',' like '%,'+CAST(PeriodBrief.PeriodBriefUID AS varchar(20))+',%'

and change @PeriodUID = '969','342','432' statement as

SET @PeriodUID = '969,342,432'

if PeriodBrief.PeriodBriefUID is int modify as above

Good Catch Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:18:29

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources