Posted by Erland Sommarskog on 12/18/07 22:41

Artie (artie2269@yahoo.com) writes:
> The Parent_qty seems to work until you have to move back up the
> hierarchy level. See 'Engine'. Its parent_qty should be 1 (1 engine
> per car), not 5.

Obviously, you need to restore @Parent_qty to be for the previous level.

Rather than rewriting the procedure, I offer a different solution,
using a recursive procedure (which has the drawback that it will
not handler more than 32 levels).

CREATE PROCEDURE expand @item varchar(20),
@lvl tinyint = 1,
@qty int = 1,
@parent_qty int = NULL AS

DECLARE @child varchar(20)

IF @lvl = 1
BEGIN
CREATE TABLE #output(rowno int IDENTITY,
lvl tinyint NOT NULL,
item varchar(20) NOT NULL,
qty int NOT NULL,
parent_qty int NULL)
END

INSERT #output(lvl, item, qty, parent_qty)
VALUES (@lvl, @item, @qty, @parent_qty)

SELECT @lvl = @lvl + 1, @parent_qty = @qty

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Child, qty FROM Hierarchy WHERE Parent = @item
OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @child, @qty
IF @@fetch_status <> 0
BREAK

EXEC expand @child, @lvl, @qty, @parent_qty
END

DEALLOCATE cur

IF @lvl = 2
BEGIN
SELECT space(lvl) + item, qty, parent_qty
FROM #output
ORDER BY rowno
END
go


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]