Problems with an insert

Question

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)

Answer 1

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))

Answer 2

Thanks Tom, I got so confused.  Thanks much, it works great now!