Why am I getting zeros for each New Loop call? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why am I getting zeros for each New Loop call?

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 15:15:01

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)

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 16:02:05

just a guess, but it looks like you are always putting a comma in the beginning of your string (@tmpstr) on the first pass through, and probably interpreting that later on as if the string begins with 0. (i.e., converting '' to BigInt results in 0).

quote:

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)

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 16:07:13

[code]DECLARE @CurrentProductID BIGINT,
@MaxProductID BIGINT,
@ChildIDs VARCHAR(8000)

SELECT @CurrentProductID = MIN(ProductID),
@MaxProductID = MAX(ProductID)
FROM @Tab

WHILE @CurrentProductID <= @MaxProductID
BEGIN
SELECT @ChildIDs = REPLACE(ChildIDs, ' ', '')
FROM @Tab
WHERE ProductID = @CurrentProductID

INSERT ProductRelationship
(
ProductID,
RelatedProductID
)
SELECT @CurrentProductID,
Data
FROM dbo.fnParseList(',', @Data)

SELECT @CurrentProductID = MIN(ProductID)
FROM @Tab
WHERE ProductID > @CurrentProductID
END[/code]
Using this function below
[code]
CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@Text)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
END

INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
END

RETURN
END[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources