[RESOLVED] SQL Server '05: Split and loop

[aconybeare]

Hi,

I want to pass a delimited string to a Stored Proc split it and loop through it updating records as necessary. I can't work out how to assign a table to a table variable. Below is what I have so far, I've highlighted the problem line

DECLARE @Ids varchar(100) SET @Ids='100,99,102,105'
DECLARE @tmpTable TABLE SET @tmpTable=dbo.Split(@Ids,',')
DECLARE @varMaxCount SMALLINT SET @varMaxCount=(SELECT MAX(Id) FROM @tmpTable)
DECLARE @varCurrentCount SMALLINT SET @varCurrentCount=1 

DECLARE @Id varchar(100) -- int

WHILE (@varCurrentCount <=@varMaxCount) BEGIN
	SET @Id=(SELECT id FROM @tmpTable WHERE ID = @varCurrentCount)
	Print 'Id: '+Convert(varchar,@Id)
	SET @varCurrentCount = @varCurrentCount + 1
END

Split UDF -

	CREATE FUNCTION dbo.Split
	(
		@List nvarchar(2000),
		@SplitOn nvarchar(5)
	)  
	RETURNS @RtnValue table 
	(
		Id int identity(1,1),
		Value nvarchar(100)
	) 
	AS  
	BEGIN 
		While (Charindex(@SplitOn,@List)>0) Begin
			Insert Into @RtnValue (value)
			Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
		End

		Insert Into @RtnValue (Value)
		Select Value = ltrim(rtrim(@List))
	
		Return
	END

Any help will be greatly appreciated

Al

[FunkyDexter]

Why assign it to a variable at all? Simply treat the function call as a table in your query:-

SET @Id=(SELECT id FROM dbo.Split(@Ids,',') WHERE ID = @varCurrentCount)

[aconybeare]

FunkyDexter,

Excellent idea, thanks a lot!!

For completeness here is the working example -

DECLARE @Ids varchar(100) SET @Ids='100,99,102,105'
DECLARE @Id int
DECLARE @varMaxCount SMALLINT SET @varMaxCount=(SELECT MAX(Id) FROM dbo.Split(@Ids,','))
DECLARE @varCurrentCount SMALLINT SET @varCurrentCount=1 

WHILE (@varCurrentCount <=@varMaxCount) BEGIN
	SET @Id=(SELECT Convert(int,Value) FROM dbo.Split(@Ids,',') WHERE ID = @varCurrentCount)
	Print 'Extracted Id: '+Convert(varchar,@Id)
	SET @varCurrentCount = @varCurrentCount + 1
END