Error in converting varchar to int, plz solve this

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

General SQL Server Forums

New to SQL Server Programming

Error in converting varchar to int, plz solve this

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-06-08 : 06:26:26

Hai All

declare @var varchar(32)
set @var = '101,102,103,104,105,106'
select * from emp where emp id in (cast(@var as int))

This is my query, when am trying to execute this, am getting error that

Failed to convert varchar value '101,102,103,104,105,106' to datatype int

Anyone plz solve this

Thanks in Advance

Suresh Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 06:38:53

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

E 12°55'05.63"
N 56°04'39.26"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-08 : 06:40:08

You can do like this..

You must use Dynamic SQL.

set @query='select * from emp where emp_id in ('+@var+')'

Exec (@query)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 06:47:35

I hope someone doesn't enter the value " 1); DROP TABLE Emp"

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-08 : 07:10:22

select * from emp where '%,' + @var+ ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-08 : 08:47:27