Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to pass variable to the IN clause

Author  Topic 

timlisten
Starting Member

26 Posts

Posted - 2012-06-11 : 18:30:28
Hi, I have a query where I need to use the IN variable. Is there a way to do it without using dynamic query? For example,
select *
from table
where data IN ('A', 'B')

works fine. Is there a way for me to pass a variable to the IN clause instead of the static 'A', 'B'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 18:32:43
you can see



DECLARE @VarList varchar(1000)

SET @VarList = 'A,B'

SELECT *
FROM table
WHERE ',' + @VarList + ',' LIKE '%,' + data + ',%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

timlisten
Starting Member

26 Posts

Posted - 2012-06-11 : 21:44:11
that works great, thank you so much visakh16. I just wished I had come to this forum years ago.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:19:33
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2012-06-12 : 15:48:19
[code]
DECLARE @VarList varchar(1000)

SET @VarList = 'A,B'

SELECT *
FROM table
WHERE data IN(select @VarList)
[/code]

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 20:19:35
quote:
Originally posted by Sachin.Nand


DECLARE @VarList varchar(1000)

SET @VarList = 'A,B'

SELECT *
FROM table
WHERE data IN(select @VarList)


After Monday and Tuesday even the calendar says W T F ....


sorry didnt understand how this will work.

check below code


DECLARE @VarList varchar(1000)

declare @t table
(
id int identity(1,1),
chr char(1)
)
insert @t (chr)
values ('A'),('C'),('D'),('F')

SET @VarList = 'A,F'

SELECT *
FROM @t
WHERE chr IN(select @VarList)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2012-06-13 : 02:47:56
quote:
Originally posted by visakh16

quote:
Originally posted by Sachin.Nand


DECLARE @VarList varchar(1000)

SET @VarList = 'A,B'

SELECT *
FROM table
WHERE data IN(select @VarList)


After Monday and Tuesday even the calendar says W T F ....


sorry didnt understand how this will work.

check below code


DECLARE @VarList varchar(1000)

declare @t table
(
id int identity(1,1),
chr char(1)
)
insert @t (chr)
values ('A'),('C'),('D'),('F')

SET @VarList = 'A,F'

SELECT *
FROM @t
WHERE chr IN(select @VarList)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Naah..Just made a wild guess..

Dont take it to seriously..

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:50:46
np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-06-14 : 00:13:21
If your table is large, use function for better performance.
Go to Top of Page
   

- Advertisement -