Get Ancestor and Descendant in a Hierarchy

For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about Recursive CTEs in SQL Server Books Online, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.

And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.

First of all we have to create a table for the following functions and build some test data. The statemens look like:

create table ST_CATEGORY(
CATEGORYID uniqueidentifier not null default NEWID(),
PARENTID uniqueidentifier,
[NAME] varchar(128),
COMMENT varchar(4096),
CONSTRAINT PK_ST_CATEGORY primary key (CATEGORYID)
)
go

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.rootoid, NULL, 'ROOT', 'ROOT NODE')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.rootoid, 'Business Application', 'group for all business applications')

declare @.techoid uniqueidentifier
set @.techoid = NEWID()

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(@.techoid, @.rootoid, 'Tech101', 'technical tips')

insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT)
values(NEWID(), @.techoid, 'C#', 'C# tips')
go

Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.

CREATE PROCEDURE spGetAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find parents/ancestor
WITH Ancestor( CATEGORYID ) AS
(
SELECT PARENTID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM Ancestor, ST_CATEGORY
WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM Ancestor
END
GO

CREATE PROCEDURE spGetSelfAndAncestor
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and parents/ancestor
WITH SelfAndAncestor( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT PARENTID
FROM SelfAndAncestor, ST_CATEGORY
WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID
)
SELECT * FROM SelfAndAncestor
END
GO

CREATE PROCEDURE spGetDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find children/descendant
WITH Descendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE PARENTID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM Descendant, ST_CATEGORY
WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM Descendant
END
GO

CREATE PROCEDURE spGetSelfAndDescendant
@.categoryID uniqueidentifier
AS
BEGIN
-- find self and children/descendant
WITH SelfAndDescendant( CATEGORYID ) AS
(
SELECT CATEGORYID
FROM ST_CATEGORY
WHERE CATEGORYID = @.categoryID
UNION ALL
SELECT ST_CATEGORY.CATEGORYID
FROM SelfAndDescendant, ST_CATEGORY
WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID
)
SELECT * FROM SelfAndDescendant
END
GOUhm......OK. Thanks for sharing with us information that is available in Books Online...