Variable which Stores multiple Values

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)

Variable which Stores multiple Values

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-23 : 08:02:34

Hi From the Application i would be getting a set of Employee Numbers for which i need to return their respective names.They would send say more than 10 employee numbers everything concatenated.
I've saved this values in a variable and trying to use the IN clause(Since it is tough to use the select statemet everytime) and get the Employee name like the Statements shown below-

Declare @test Varchar(50)
set @test=('1','2','3','4','5','6','7','8')
select employee_nbr,first_nm from temployee where employee_nbr in (@test)

But when i execute this it is showing the below mentioned error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Can anyone help in getting rid of this error. suggest me a better way to store all the values in a variable coz when i execute this Query it does not who any error and gives me the output

select employee_nbr,first_nm from temployee where employee_nbr in ('1','2','3','4','5','6','7','8')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 08:13:35

a single quote would be masked by another single quote.
select @test=' ''1'',''2'' '
print @test

Webfred

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 08:15:01

try like this


Declare @test Varchar(50)
set @test='1,2,3,4,5,6,7,8'
select employee_nbr,first_nm from temployee where ','+@test+',' like '%,'+cast(employee_nbr as varchar(10)) +',%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-23 : 08:27:53

Refer this to know how single quotes work in SQL Server
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-23 : 08:49:48

Thanks a Lot Vishak. Your Solutions Always helped me

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-23 : 08:52:00

Hi
Madhivanan Your Blog was very much informative . Thanks