Using Variable in IN Clause

In SQL Server, if you pass a comma separated list as a variable in IN Clause in T-SQL, it would not give error but you will not even get expected result either.

In this article, we will see how to overcome this with example

Let's first see the problem encountered while using variable in "IN" clause.

First create a table with some sample data,

create table tbldata
   (
   id int, name varchar(50)
   )
insert into tbldata
select 1, 'Sandeep' union all
select 2, 'Abhay' union all
select 3, 'Ritesh'

Now, let's execute the below queries with and without variable in "IN" clause.

select * from tbldata where name in ('Sandeep', 'Abhay')

OUTPUT



Here, we get the result as expected, now let's execute query with variable,

declare @idList varchar(max)
set @idList = '''Sandeep'', ''Abhay'''
select * from tbldata where name in (@idList)

OUTPUT



See here when we execute the same query with variable, we did not get the expected output. Hence, it is concluded that variable does not work fine when used inside "IN" clause in Sql Server.

Now, let’s see the work around to this problem. There are two solutions to handle the same,

1. Dynamic query
   

   declare @query nvarchar(max), @idList varchar(max)
   set @idList = '''Sandeep'', ''Abhay'''
   set @query = 'select * from tbldata where name in (' + @idList + ')'
   exec sp_executesql @query

2. Split function
   
   First create split function using link: split function in SQL.Once split function is created, then use the below solution.
   

   declare @idList varchar(max)
   set @idList = 'Sandeep,Abhay'
   select * from tbldata where name in (select val from dbo.split(@idList,','))

Replacing IN with JOIN

Split is a tabular function which returns table of the list passed to it. In the above example IN can be replaced with JOIN.

declare @idList varchar(max)
set @idList = 'Sandeep,Abhay'
select t.* from tbldata t
inner join dbo.split(@idList,',') s on t.name = s.val