Problems with an insert

Ok, I've been looking at my code for a while now and need some help. All this function should do is take the incoming ProductID and String of ChildIDs, then insert a record for each ProductID and ChildID into my table

I have 2 problems with my function right now:

1) Why is my function creating zeros in the first pastthrough the loope for new ProductID passed to my function?

ProductID RelatedProductID
105744 0
0 105744
...and then it's fine

The problem is that the @.str variable is returning zero in the first iteration in my loop

SET @.str = substring(@.tmpstr, 1, @.pos - 1

2) It's inserting dup ChildIDs as you'll see below. You'll see I want to perform 2 inserts (with 2 different relationship types of 2 and 3) because for each parent / child we need a reverse insert with relationshiptype as the third column...don't ask me why, we just do.
Example incoming ChildIDs string looks like this (and this string is not always a fixed length): 105234,105235,105236,105237,105238,105239,105240,105241,105242,105243,105244,105245,105246,105247,105248,105249,105250,105251,105252,105253,105254,105255

The product ID incoming that is passed to this function for example is 106731

Example of the double inserts and zeros that keep happening for a product:

106731 0
0 106731
106731 105234 ?- Double Child 105234 (which I do not want happening)
106731 105234 ?-
106731 105235
106731 105235
106731 105236
106731 105236
106731 105237
106731 105237
106731 105238
106731 105238
106731 105239
106731 105239
106731 105240
106731 105240
106731 105241
106731 105241
106731 105242
106731 105242
106731 105243
106731 105243
106731 105244
106731 105244
106731 105245
106731 105245
106731 105246
106731 105246
106731 105247
106731 105247
106731 105248
106731 105248
106731 105249
106731 105249
106731 105250
106731 105250
106731 105251
106731 105251
106731 105252
106731 105252
106731 105253
106731 105253
106731 105254
106731 105254
106731 105255
105253 106731
105251 106731
105249 106731
105247 106731
105245 106731
105243 106731
105241 106731
105239 106731
105252 106731
105254 106731
105237 106731
105235 106731
105250 106731
105248 106731
105246 106731
105244 106731
105242 106731
105240 106731
105238 106731
105236 106731
105234 106731 <-- Double ChildID 105234
105234 106731 <--
105236 106731
105235 106731
105244 106731
105243 106731
105242 106731
105241 106731
105240 106731
105239 106731
105238 106731
105237 106731
105254 106731
105253 106731
105255 106731
105252 106731
105251 106731
105250 106731
105249 106731
105248 106731
105247 106731
105246 106731
105245 106731

What I should be getting is this instead (example below: ProductID is in bold, ChildID is in blue):

ProductID RelatedProductID RelationshipType

106731 105234 3

105234 106731 2

106731 105235 3

105235 106731 2

106731 105236 3

105236 106731 2

106731 105237 3

105237 106731 2

106731 105238 3

105238 106731 2

106731 105239 3

105239 106731 2

106731 105240 3

105240 106731 2

…and so on

ALTER PROCEDURE [dbo].[Insert_Product_Child_Relationships]
-- Add the parameters for the stored procedure here
@.ProductID bigint,
@.ChildIDList ntext
AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ','
WHILE @.textpos <= datalength(@.ChildIDList) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = ltrim(@.leftover + substring(@.ChildIDList, @.textpos, @.chunklen))
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.str = substring(@.tmpstr, 1, @.pos - 1)
INSERT INTO ProductRelationship VALUES(@.ProductID, convert(bigint, @.str), 3,'', GetDate(), 3, NULL, NULL, 1, 0)
INSERT INTO ProductRelationship VALUES(convert(bigint, @.str), @.ProductID, 2,'', GetDate(), 3, NULL, NULL, 1, 0)
SET @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.leftover = @.tmpstr
END
END

Schema (just enough to see what's going on)

ProductRelationship

-

ProductID (bigint)

RelatedProductID (bigint)

ProductRelationshipType (int)

It is doing that because you are adding a @.leftover to the beginning of the string, so the first record is nothing.

Change your SET @.tmpstr to:

--SET @.tmpstr = ltrim(@.leftover + substring(@.ChildIDList, @.textpos, @.chunklen))
SET @.tmpstr = ltrim(substring(@.ChildIDList, @.textpos, @.chunklen))|||Thanks Tom, I got so confused. Thanks much, it works great now!