Help converting varchar to int to use in where clause

[meckeard]

All,

I am passing in the following varchar value into a
stored proc:

'4,5'

These 2 values are ID's for counties that are
seperated by a comma.

I want to do a select like this but get an error about
converting the varchar value '4,5' to a column of data
type int:

select County_ID, County_Name
from tblCounties
where County_ID in (@County_ID_List)

I tried the following with no luck:

set @County_ID_List = convert(int, @County_ID_List)

How can I convert this value to an int so that I cam
use it in my where clause?

Thanks,
Mark

 

[amrita418]

You need to use dynamic sql to do that.

Sample Code:

declare 
@v_string 	varchar(30)
set 		@v_string = '10248, 10250'
exec ('select * from Northwind..orders where orderid in (' + @v_string + ')')

Regards,
AA

 

[SQLSister]

Dynamic SQL is bad. If you use it you must set permissions on the table instead of the sp, so it is less secure. It is also frequently less efficient. Never use dynamic SQL if there is another alternative.

I would use two input parameters myself. One for each int.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mrdenny]

Another option would be to break the data in the variable apart and place the data into a table variable. Then you can do a standard where column in (select column from table)

select County_ID, County_Name
from tblCounties
where County_ID in (select Country_ID from @County_ID_List)

You can use substring, and charindex to break this variable apart.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)