[RESOLVED] SQL Server '05: Split and loop

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

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

Code:

---

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

---

FunkyDexter,

Excellent idea, thanks a lot!!

For completeness here is the working example -

Code:

---

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

---