Passing Array with ids to stored procedure

I want to pass and array of ids to a procedure for inserting i a relation table.

I found some examples in other posts, but had problems getting them to work.

I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.

(I´m using sql server 2000), had some problem with examples with strpos then.

Any hints ?

peace.

Create procedure ParseArray
( @.Array varchar(1000),
@.separator char(1) )
AS
-- Created by graz@.sqlteam.com
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
go

enough documentation to xplain whats going on...

hth|||Don't bother using arrays (well until Yukon) this is one area where passing XML is helpful. Construct a simple XML string <r><i x="10" /><i x="10"/></r> etc and pass that as text to the proc (if you're client object supports serialization the xml might already be there). In the proc convert the XML into a table var. Then off you go, the array is now a cell per row.