How to pass variable to the IN clause

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/

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

welcome

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

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 ....

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/

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/