denna sp plockar ut parent och childs och listar dem, förutom när två parents(null) ligger efter varann i tabellen, nån som fattar varför??

ID PARENT NAME
1 null parent1
2 null parent2
3 2 child1
osv

CREATE PROCEDURE getChild AS
SET NOCOUNT ON
DECLARE @level int, @line char(20), @name char(100), @PGID int

CREATE TABLE #temp (item char(20), name char(100), level int)

SET @PGID= (SELECT Min(PGID) FROM tblProductGroups WHERE Parent = NULL)
WHILE @PGID Is Not Null
BEGIN
PRINT @PGID
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@PGID, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @PGID = item
FROM #stack
WHERE level = @level
SELECT @name = Name FROM tblProductGroups WHERE PGID = @PGID
INSERT INTO #temp VALUES (@PGID, @name, @level)
DELETE FROM #stack
WHERE level = @level
AND item = @PGID
INSERT #stack
SELECT PGID, @level + 1
FROM tblProductGroups
WHERE Parent = @PGID
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE

DROP TABLE #stack
SET @PGID= (SELECT Min(PGID) FROM tblProductGroups WHERE PGID >@PGID AND Parent = NULL)
END
SELECT * FROM #temp
GO

Du kan ju testa med:

ALTER PROCEDURE getChild AS

SET NOCOUNT ON

DECLARE @level int
DECLARE @line char(20)
DECLARE @PGID int

CREATE TABLE #temp
(
item char(20),
name char(100),
level int
)

CREATE TABLE #stack
(
item char(20),
level int
)

SET @level = 1

INSERT #stack
SELECT PGID, @level
FROM tblProductGroups
WHERE Parent Is Null

WHILE @level > 0
BEGIN
SET @PGID = Null

SELECT TOP 1 @PGID = item
FROM #stack
WHERE level = @level

IF @PGID Is Null
SELECT @level = @level - 1
ELSE BEGIN
INSERT INTO #temp
SELECT @PGID, Name, @level
FROM tblProductGroups
WHERE PGID = @PGID

DELETE FROM #stack
WHERE item = @PGID

INSERT #stack
SELECT PGID, @level + 1
FROM tblProductGroups
WHERE Parent = @PGID
IF @@ROWCOUNT > 0 SET @level = @level + 1
END
END

DROP TABLE #stack

SELECT * FROM #temp

DROP TABLE #temp
GO

du är ju grym på sp :-)

Jag är grym på det mesta. ;-)