-2

The below sql statement is not resulting any records.

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar|Gujarat|Jharkhand','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

This works

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

This also works

Declare @sSelectedStates as nvarchar(500) = REPLACE('Gujarat','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

What I am doing wrong?

Using the below statement I changed the database compatibility level

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
Sixthsense
  • 1,927
  • 2
  • 16
  • 38
  • 3
    None of those work - you can't use a variable in an `IN` clause. The ones that work are only by accident because you only have a single value. – Dale K Jun 19 '23 at 10:19
  • You have: `select * from MyTable where State IN ('Bihar,Gujarat,Jharkhand')`. This is one string. You are searching for all rows where the state is 'Bihar,Gujarat,Jharkhand'. – Thorsten Kettner Jun 19 '23 at 10:19
  • @Dale K, you were wrong. I am getting result from 2nd and 3rd query using variable in In class. – Sixthsense Jun 19 '23 at 10:23
  • 1
    Did you not understand the second sentence in Dale's comment? – Stu Jun 19 '23 at 10:26
  • @Thorsten Kettner, I am using replace to make it as indivisuals. Please check once again. – Sixthsense Jun 19 '23 at 10:26
  • @Stu, Ok... Now I undetstood. Please anyone let me know how to handle this? – Sixthsense Jun 19 '23 at 10:28
  • 1
    Use `STRING_SPLIT`. Docs: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16 – Thorsten Kettner Jun 19 '23 at 10:30

2 Answers2

1

You can't do it like this. The IN operator expects a list of parameters separated by comma, but you supply it with a single parameter that happens to contain a comma delimited string.

A scalar variable can only contain a single value - and when the IN operator is presented with a single value, it acts like the = operator - so your queries are basically the same as these:

select * from MyTable where State = 'Bihar'',''Gujarat'',''Jharkhand'

select * from MyTable where State = Bihar

select * from MyTable where State = Gujarat

If you're working with SQL Server 2016 or higher, you can use the built in string_split to convert the delimited string into a table.
If you're working on a lower version, you can use a user-defined function to split the string for you.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • I am getting the state names from a variable. So cant use it directly in where class – Sixthsense Jun 19 '23 at 10:45
  • https://stackoverflow.com/a/49849515/3094533 This is a basically similar answer to a basically similar question I've once written. It has links to string-splitting UDFs that can be used in SQL Server versions that predates the 2016 version. – Zohar Peled Jun 19 '23 at 11:01
1

This is quite a common misconception, in works on a set not a string - it does not parse the single string you are providing in any way.

You can provide a set of values to the in clause by splitting the string into rows:

Declare @sSelectedStates nvarchar(500) = 'Bihar|Gujarat|Jharkhand';

select * 
from MyTable 
where State in (select * from String_Split(@sSelectedStates, '|'));
Stu
  • 30,392
  • 6
  • 14
  • 33