Posted by Jens on 10/21/04 11:26

Try that:

IF OBJECT_ID('Tree') IS NOT NULL
DROP Table Tree

CREATE TABLE Tree
(
Parent INT,
Child INT
)

INSERT INTO Tree
SELECT 100,200


INSERT INTO Tree
SELECT 100,300


INSERT INTO Tree
SELECT 100,400


INSERT INTO Tree
SELECT 400,500


INSERT INTO Tree
SELECT 500,1000

DROP FUNCTION getHierarchy

GO

CREATE FUNCTION getHierarchy
(
@child INT
)
RETURNS @Hierarchy TABLE
(
Path INT
)
AS
BEGIN

IF NOT EXISTS (SELECT * from Tree where child = @child)
BEGIN
INSERT INTO @Hierarchy
SELECT NULL
SET @child = NULL
END

WHILE (@Child IS NOT NULL OR LEN(@Child) = 0)
BEGIN
INSERT INTO @Hierarchy
SELECT @Child

SELECT @Child = Parent from Tree where child = @child
IF @@ROWCOUNT = 0 SET @Child = NULL
END

RETURN
END

GO

Select * from dbo.getHierarchy(500)

HTH, Jens Suesmeyer

---
http://www.sqlserver2005.de
---

• Next in forum: Re: Return from store procedure.
• Prev in forum: Re: Return from store procedure.
• Thread view: Re: How do I write a query to get the path from ancestor to node in a tree?

[Reply to this message]