June 13, 2008 at 4:59 am
Hello everyone i m using SQL Server2005 ,i have to check some data in my Cities table so i used the following command
Declare @myTestVar varchar(100)
set @myTestVar = '10,11,12,13'
select * from cities where CityID in (@myTestVar)
But it gives following error : "Syntax error converting the varchar value '10,11,12,13' to a column of data type int."
Than i used convert function in my query
Declare @myTestVar varchar(100)
set @myTestVar = '10,11,12,13'
select * from cities where convert(varchar(100),CityID) in (@myTestVar) But it "Returns Nothing".
Than i make a dynamic sql and used this it returns the accurate result.
Declare @myTestVar varchar(100)
set @myTestVar = '10,11,12,13'
declare @SQL nvarchar(4000)
select @SQL = 'select * from Cities where CityID in ('+ @myTestVar+ ')'
print @SQL
Exec sp_executesql @SQL
its strange, i dont want to use dynamic query for testing my tables data. so kindly help me in writing simple select stmt how i passed the
Comma seperated values, I dont want to use subqueryies for these types of simply queries.
i know i can write this and it worls fine: select * from cities where CityID in (10,11,12,13) but i want to check this with the variable not pass direct values in
my this stmt.
kindly help me in this regrad. Thanx in Advance.
June 13, 2008 at 7:22 am
isa (6/13/2008)
Hello everyone i m using SQL Server2005 ,i have to check some data in my Cities table so i used the following commandDeclare @myTestVar varchar(100)
set @myTestVar = '10,11,12,13'
select * from cities where CityID in (@myTestVar)
My approach would be to convert the CityID to a String and call CHARINDEX to see if it is in the string that holds the list of IDs.
select * from cities where CHARINDEX(',' + CAST(CityID as varchar(20)) + ',', ',' + @myTestVar + ',') > 0
Why am I prepending and appending extra commas?
1) To avoid false matches. e.g. '12' matching '123' because it is a substring.
2) To poperly match if the ID is first or last in the list.
Thus if I am looking for '13', my search will look for ',13,' in ',10,11,12,13,' and match correctly.
- Paul
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply