Simple Category Tree Walking In T-SQL

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

--------------------