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

Code:
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 -
Code:
	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