Using Variable in "IN" clause in SQL Server

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.

I have seen most of the developers facing the problem while using variable in "IN" clause in SQL Server in early stage of their career. So, I decided to post an article on 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, lets 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
OUTPUT

2. Split function
First create split function using link:  Split Function in SQL Server
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,','))
OUTPUT

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
OUTPUT

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Previous
Next Post »

8 comments

comments
April 16, 2013 at 10:21 AM delete

Thank a lot for the comment .... :)

Reply
avatar
August 2, 2013 at 8:48 PM delete

and for every DBMS that have not dynamic executions, or a bad performance, use:
select * from tbldata where instr ('|' || name || '|','|Sandeep||Abhay|')

The appended(delimitator) pipe character | can be replaced with a character of your choice.

Reply
avatar
November 6, 2013 at 2:15 AM delete

though not efficient but we can use charindex here to accomplish the same

Reply
avatar
November 16, 2013 at 11:12 PM delete

It shouldn't be done that way. If you have a dynamic list to check against, just put it in a table variable and join to that table. Your solutions (well, hacks, not solutions) have many rough edges...

Reply
avatar
November 17, 2013 at 12:30 PM delete

Dear Dog,
The idea here is to explain the problem while using variable in "IN" clause.
However, if you will go through the post carefully, in the last step it is done the same way you are suggesting and i would also suggest to use the same
Thanks.

Reply
avatar