Artie (artie2269 [at] 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 [at] 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 [at] item varchar(20),
[at] lvl tinyint = 1,
[at] qty int = 1,
[at] parent_qty int = NULL AS
DECLARE [at] child varchar(20)
IF [at] 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 ( [at] lvl, [at] item, [at] qty, [at] parent_qty)
SELECT [at] lvl = [at] lvl + 1, [at] parent_qty = [at] qty
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Child, qty FROM Hierarchy WHERE Parent = [at] item
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO [at] child, [at] qty
IF [at] [at] fetch_status <> 0
BREAK
EXEC expand [at] child, [at] lvl, [at] qty, [at] parent_qty
END
DEALLOCATE cur
IF [at] lvl = 2
BEGIN
SELECT space(lvl) + item, qty, parent_qty
FROM #output
ORDER BY rowno
END
go
--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
