SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select convert a varcharmax to int		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sebastian11c
Posting Yak Master

125 Posts
Posted - 05/22/2012 :  11:02:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts
Posted - 05/22/2012 :  11:17:04  Show Profile  Reply with Quote
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.
select make 
from Cars
where idcars in (select value from dbo.Split(@array))
I'd go with option #2

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000