IN with Varchar is not working sql server

Question

Below code is throwing error

declare @test varchar(100)='20,201,202,288'
SELECT * from Business where Businessid in (@test)

i.e. Conversion failed when converting the varchar value '20,201,202,288' to data type int.

how can i make it working, i'm not getting, might be my mind is not able think any more..

Does this answer your question? [How do I pass a variable that contains a list to a dynamic SQL query?](https://stackoverflow.com/questions/54159434/how-do-i-pass-a-variable-that-contains-a-list-to-a-dynamic-sql-query) — Ilyes, Jun 06 '20 at 20:10

score 8 · Accepted Answer · answered Jun 06 '20 at 20:09

8

You can use string_split():

select *
from Business
where Businessid in (select value from string_split(@test, ','));

You are confusing a string with a list of values in an in list. They are not the same thing.

answered Jun 06 '20 at 20:09

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 06 '20 at 23:45

We can use string_split in SQL Server 2016 and later versions only so I choose dynamic query for any version with more flexibility

DECLARE @test VARCHAR(100) = '20,201,202,288', @sql VARCHAR(250)

SET @sql = '
SELECT * 
FROM Business 
WHERE Businessid IN (' + @test + ')'

EXEC(@sql)

IN with Varchar is not working sql server

2 Answers2