select convert a varcharmax to int

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

Transact-SQL (2008)

select convert a varcharmax to int

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-22 : 11:02:39

hi there

i have a SP with one variable @array varchar(max)

i get the variable from a web application for example

@array=' 3, 6, 5 ' ( i cannot change the structure of this variable)

and i have a table called "cars"

idcar (int) make
1 audi
2 bmw
3 mercedes benz
4 land rover
5 ferrari
6 fiat

and i use that variable this way

select make
from cars
where idcars in (@array)

but i get this error
Conversion failed when converting the varchar value '3, 6, 5' to data type int

any idea to make this sp succed???
remember that i cannot change the structure of the variable @array because i get from a web application

many thanks in advanced

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-22 : 11:17:04

It is not possible to use a variable in an IN operator this way. There are two options that I can think of; one of which I can recommend.
1) Use dynamic SQL (ugh!)
2) Use a table valued function which accepts your variable as an input and returns the values in the table

Ex.
[CODE]select make
from Cars
where idcars in (select value from dbo.Split(@array))[/CODE]I'd go with option #2

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources