I needed, as many have done before me, to create a snippet of SQL which was short and fast and returned a flattened table of tree nodes. In short, if you have a tree of nodes, get all the nodes below a specified node. The result is surprisingly simple and straightforward. Here it is, both as a snippet and a stored procedure. Assume the tree is stored in a table called Category, with CategoryID being the primary key and ParentCategoryID being the foreign key, to the same table, but nullable...
--------------------
declare @collect table ( catid int, tlevel int )
insert into @collect values (3,1)
declare @count int
set @count = 2
declare @maxlevel int
set @maxlevel = 2
while ((select count(1) from @collect where tlevel = @count-1) > 0 and @count <= @maxlevel)
begin
insert into @collect
select c.categoryid, @count
from category c inner join @collect b on c.parentcategoryid = b.catid and b.tlevel = @count-1
set @count = @count + 1
end
select * from @collect order by tlevel
--------------------
--------------------
CREATE PROCEDURE [dbo].[GetCategoryTree]
-- Add the parameters for the stored procedure here
@ParentCategoryID int
AS
BEGIN
-- the category id's to be output
declare @collect table ( catid int, tlevel int )
insert into @collect values (3,1)
-- a loop counter used to limit the depth we go to
declare @count int
set @count = 2
-- the deepest level to go to
declare @maxlevel int
set @maxlevel = 2
-- the loop to collect category id's
while
(
(select count(1) from @collect where tlevel = @count-1) > 0 -- keep going while we have categories
and @count <= @maxlevel -- keep going until the max level depth is reached
)
begin
-- insert the categories where the previous level contains the parent ids of the current level
insert into @collect
select c.categoryid, @count
from category c inner join @collect b on c.parentcategoryid = b.catid and b.tlevel = @count-1
-- increment the level being collected
set @count = @count + 1
end
-- return the found category id's
select * from @collect order by tlevel
END
--------------------
