denna söker neråt...

CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

Menar du från ett item rakt till rooten?

ja men jag fixxade det såhär

CREATE PROCEDURE getPGParentsChilds (@PGIDin int) AS
SET NOCOUNT ON
DECLARE @PGID int
DECLARE @Parent int
CREATE TABLE #temp
(
ID int
)
SELECT @Parent = Parent, @PGID = PGID
FROM dbo.tblProductGroups
WHERE (PGID = @PGIDin)

WHILE NOT @Parent IS NULL
BEGIN
SELECT @Parent = Parent, @PGID = PGID
FROM dbo.tblProductGroups
WHERE (PGID = @Parent)

INSERT INTO #temp
SELECT PGID
FROM dbo.tblProductGroups
WHERE (PGID = @PGID)
END
SELECT * FROM #temp
DROP TABLE #temp

Jag föreslår:

CREATE PROCEDURE getPGParentsChilds (@PGIDin int) AS
SET NOCOUNT ON
DECLARE @Parent int
CREATE TABLE #temp
(
ID int
)
SELECT @Parent = Parent
FROM dbo.tblProductGroups
WHERE (PGID = @PGIDin)

WHILE NOT @Parent IS NULL
BEGIN
INSERT INTO #temp VALUES(@Parent)

SELECT @Parent = Parent
FROM dbo.tblProductGroups
WHERE (PGID = @Parent)
END
SELECT * FROM #temp
DROP TABLE #temp