Select Query is not working with in Clause

Question

Sql query is not working if we pass value In clause converted from 'Canada,Portugal' to 'Canada','Portugal' if pass hardcode value In clause 'Canada','Portugal' it works

  declare @GeographicalLocation varchar(max) 
        set @GeographicalLocation ='Canada,Portugal'
        set @GeographicalLocation  = REPLACE(@GeographicalLocation, ',', ''',''')
        set @GeographicalLocation = ''''+@GeographicalLocation+'''';
select ContinentName from [ContinentList] where ContinentId in 
    (select ContinentId from [CountryList] where [CountryName] 
     in(@GeographicalLocation)and BaseId is Null)

You're not passing in a list, you're passing in a single string containing the value `Canada,Portugal` and it's not possible to use strings in `in` clause — James Z, Mar 31 '18 at 11:53

score 0 · Answer 1 · answered Mar 31 '18 at 11:51

Because, at the end your where clause looks like:

where [CountryName] in ('''Canada'',''Portugal''')

This string in where caluse should be two separate strings, but it's only one! It should look like this:

where [CountryName] in ('Canada','Portugal')

So, for this situation I'd use something like

where charindex([CountryName], @GeographicalLocation) > 0

In this approach, you don't need to append extra ' to your string variable. And I'd recommend using case sensitive collation.

Select Query is not working with in Clause

1 Answers1