use a varchar variable data seperated by Commas inside the IN clause of where statemnt

And if you want to go that way, also be sure to read this[^] (really, do it or this will happen![^])
You have been warned :)

Unfortunately, there is no real way to do that.

IN is a function, and expects a range of parameters. For example:

select * from #temp where a IN (A,B)

calls the IN function with two parameters. In your code, you call it with one parameter:

set @qry = 'A' + ',' + 'B'
select * from #temp where a IN(@qry)

The equilavent of writing:

select * from #temp where a IN('A,B')

Try it: add a value to your temporary table:

create table #temp
(a varchar(1000))
insert into #temp
values('A')
insert into #temp
values('B')
insert into #temp
values('A,B')
select * from #temp
 
Declare @qry varchar(100)
set @qry = 'A' + ',' + 'B'
--print @qry
select * from #temp where a IN(@qry)
drop table #temp

And you will get a row returned.

There are a few work-rounds on the net, but they are all pretty nasty, and involved constructing a string with a known delimiter and then using the INSTR function in a loop. An example is here: http://www.techrepublic.com/article/simulate-variable-arguments-in-plsql/5726322[^]

Basically, if you can find another, way to do this, I would!

Have you tried to do the same using the dynamically constructued SQL strings, given below is an example :-

create table #temp
(a varchar(1000))
insert into #temp
values('A')
insert into #temp
values('B')
insert into #temp
values('A,B')
select * from #temp

-- A variable to contain the filter condition (Comma Seperated values)
-- Notice the way i constructed the filter string

Declare @filter varchar(500)
set @filter = '''A,B'',''A'',''B'''
-- print @filter

Declare @qry varchar(1000)
set @qry = 'select * from #temp where a IN(' + @filter + ')'
-- print @qry

Exec(@qry)

drop table #temp