Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
Hi All,

Can anybody tell how to use a varchar variable data inside the IN clause of where statemnt?

My variable contains data seperated by commas which is to be used in the IN clause.

I am using following sql for R&D:
SQL
create table #temp
(a varchar(1000))
insert into #temp
values('A')
insert into #temp
values('B')
select * from #temp

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


I have come to know that I can't concatenate the comma that needs to be used in the IN clause, but still I dont know how to implement this kind of scenario.

Any help would be much appreciated.

Regards,
Gopal
Posted
Updated 20-Apr-11 21:56pm
v2
Comments
Dalek Dave 21-Apr-11 3:57am    
Edited for Grammar, Syntax and Readability.

Have a look towards the end of this article[^].
 
Share this answer
 
Comments
RDBurmon 21-Apr-11 3:16am    
Hello Gopal , Please follow the above step , That is what I suppose to give you.
gopalgupta 21-Apr-11 5:08am    
very nice article ...thnx bro But the example Example 3.1 - Using IN Operator missed out something I guess.. SET @EmpID = '1001,1003' should be used as SET @EmpID = '''1001'',''1003''' Correct me If I am wrong coz with the original statemnt it gives an error 'Invalid Column Name'
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 :)
 
Share this answer
 
Comments
Dalek Dave 21-Apr-11 3:57am    
Good Links
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!
 
Share this answer
 
Have you tried to do the same using the dynamically constructued SQL strings, given below is an example :-

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900