Saturday, March 23, 2013

using variable in "IN" clause in sql

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, today I decided to post the solution for the same on my blog to help them.

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

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

If there are any other solution(s), everyone is welcome to share the same.

8 comments:

Post a Comment