Something like this <b>might </b>work:
SET @CityId = <b>’23,45,85,86′</b>
SELECT VendorId From Vendors
WHERE CountryId = @CountryId
AND <b>CHARINDEX(‘,’+CAST(CityId as varchar)+’,’,’,’+@CityId+’,’) > 0</b>
But it would be inefficient: If you have an index on the CityId, it won’t be used.
If the number of possible values in the @CityId list has a known maximum, it would be better to have a function to split the list, and perform the query like this:
<pre>AND CityId in (@city1,@city2,…,@cityn)</pre>
Another option could be creating a function that parses the list and returns a temporary table with the values, which you could join to your Vendors table.
This last option (among others) is discussed in this interesting article:
<a href=”http://www.sommarskog.se/arrays-in-sql-2005.html”>Arrays and Lists in SQL Server 2005 and Beyond</a>