IN (@Variable) - How? - SQL Server Forums

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)

IN (@Variable) - How?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-03-26 : 17:15:55

How can I make this work?

DECLARE @cList char(50)

Set @cList = '12441,17438'

Select * from Client Where Customer_No IN (@cList)

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:21:19

See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-27 : 02:53:21

Also http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 05:59:59

You can use the Split function below:

CREATE FUNCTION [dbo].[Split] ( @ItemList NVARCHAR(4000), @delimiter CHAR(1) )
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ', ', ',')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item)
VALUES(@Item)
IF @i = 0 SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

---------------------
So the query would be something like:
declare @S varchar(1000)
set @S='1,3,5'

Select * from A
where ID in
(SELECT Item FROM split(@S, ','))

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-03-27 : 07:47:36

Thanks everyone.

CVDpr
Starting Member

41 Posts

Posted - 2009-03-27 : 11:06:45

where field ','+ @cList + ',' like '%,' + cast(field as varchar(#)) + ',%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources