Why am I getting zeros?

Question

I'm not sure why for each ProductID incoming to my function it first creates zeros like this

ProductID  RelatedProductID
105744     0
0             105744

...and then it's fine

and then it does what I want it to, splits out the incoming Product / Childs and inserts them into my table.

I don't see why the zeros are the value the first time through the loop for convert(bigint, @str) as well as ProductID

Example incoming string looks like this (just one example, it's not always fixed length meaning fixed # of ChildIDs): 105234,105235,105236,105237,105238,105239,105240,105241,105242,105243,105244,105245,105246,105247,105248,105249,105250,105251,105252,105253,105254,105255

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)
               ALTER TABLE dbo.ProductRelationship NOCHECK CONSTRAINT FK_ProductRelationship_Product
               ALTER TABLE dbo.ProductRelationship NOCHECK CONSTRAINT FK_ProductRelationship_RelatedProduct
               ALTER TABLE dbo.ProductRelationship NOCHECK CONSTRAINT FK_ProductRelationship_ProductRelationshipType
               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

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT INTO ProductRelationship  VALUES(@ProductID, convert(bigint, @leftover), 3,'', GetDate(), 3, NULL, NULL, 1, 0)
         INSERT INTO ProductRelationship  VALUES(convert(bigint, @leftover), @ProductID, 2, '', GetDate(), 3, NULL, NULL, 1, 0)     
   END

Answer 1

The first time through your loop, you're creating the @tmpstr variable by appending the first part of the @ChildIDList parameter to the @leftover variable in line 14, which at that point contains a single comma (set in line 10).  Then when you get the @pos value as the location of the first comma (line 17), it is setting @pos to the value of 1.  That means that the first time the

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

line is executed, @str is being set to an empty string.

 

Then when the insert statements are executed, the @str is converted to a bigint, which in this case is the value 0.

 

You will need to modify the procedure to handle this case.  Maybe you could add a check to ensure that the value in @str is not an empty string, since that would also handle cases when someone calls the stored procedure with an @ChildIDList parameter of something like 123,124,,135.

 

Iain

Answer 2

Ok, thanks..at least I knwo where it is.  I have a hard time following my code.  I still will not be sure how to fix this.  It's starting at 1 but I'll try working on how to fix that.  It's not an easy one and I've already spent 2 days on this.

Answer 3

I know that the @str is empty however I do not want to take the approach to "patch" this by adding an IF statement to check whether it's empty.  I'd rather fix my logic so that @str is never empty but I need help with that is what I'm saying...because my head hurts right now looking at this code...SQL coded for some reason when you get this crazy with it (remember this is just part of a larger query of mine), gives me a headache.  It's not like C# in that it's not OO so SQL is a pain in the *** when you don't normally code stuff like this as I don't

 

Answer 4

The fix is to change the line:

SET @tmpstr = ltrim(@leftover + substring(@ChildIDList, @textpos, @chunklen))

to:

SET @tmpstr = ltrim( substring(@ChildIDList, @textpos, @chunklen) + @leftover)

You want to make sure the string ends with a ',' not begins with one.

PS.  I posted this solution in your duplicate post which was apparently deleted.