Count of Descendents?

Bear with me I'm not a TSQL expert. I am writing a directory, and I want to get the count of all descendents underneath a selected parent category. I had this in done in VB code, but I want to transfer this function to a sproc.

I am able to build the structure and provide parent paths, counts, etc. The table structure consists of two tables a categories table, and sites table. Within the categories table their is a SiteCount field, which contains all sites within that specific category. Now I want to get all the descendants of the category and populate a CulCount field. The sproc I have now is the following:

ALTER procedure GetCategories

@.ParentID int,
@.moduleID int

as

DECLARE @.Level Int
DECLARE @.Rows Int
DECLARE @.id int
DECLARE @.cChildren int

SET @.Level = 0

--Temporary table
CREATE TABLE #Test2(PK Int IDENTITY (1,1), CategoryId Int, level Int, CategoryName varchar(100),SortColumn varchar(1000), Path varchar(1000), SiteCount Int, CulCount Int, DateSiteAdded datetime, ParentID Int)

INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

SELECT
CategoryID, 0, CategoryName, ';' + LTRIM(STR(@.ParentID)) + ';', 'Root > ' + Categories.CategoryName,
(SELECT Count(SiteID) FROM Sites WHERE Sites.SiteCatID = CategoryID AND SiteActive = 1) As SiteCount,
Categories.DateSiteAdded,
@.ParentID
FROM
Categories
WHERE
ParentId = @.ParentID AND ModuleID = @.ModuleId
SELECT @.Rows = @.@.RowCount

WHILE @.Rows > 0
BEGIN
INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

SELECT S.CategoryID, @.Level + 1, S.CategoryName, SortColumn + LTRIM(RTRIM(STR(T.CategoryId))) + ';', Path + ' > ' + T.CategoryName,
(Select Count(SiteID) As Count FROM Sites WHERE SiteCatID = S.CategoryID AND SiteActive = 1)As SiteCount, S.DateSiteAdded, S.ParentID
FROM #Test2 T
JOIN Categories S On S.ParentId=T.CategoryId And T.Level = @.Level

SELECT @.Rows = @.@.RowCount, @.Level = @.Level + 1
END

SELECT CategoryID, CategoryName, Path, SiteCount, DateSiteAdded, ParentID, SortColumn, CulCount
FROM #Test2 WHERE ParentID = @.ParentID
ORDER BY SortColumn, CategoryName

DROP TABLE #Test2

This provides me with almost everything I need, except the culmative count of all descendent sites, I have been working on this for days and I can't find an answer. I am trying to stay away from using recursive functions for this so I can support a larger variety of databases if needed. Thanks for the assist.While @.@.Rowcount > 0
update #Test2
set CulCount = SiteCount + ChildSites
from #Test2
inner join --Get the count of sites from children
(select #Test2.PK, sum(Children.CulCount) ChildSites
from #Test2
inner join #Test2 Chilren on #Test2.PK = Chilren.ParentID) ChildTotals
on #Test2.PK = ChildTotals.PK
where not exists --Only update the lowest level that has not been updated
(select *
from #Test2 SubTable
where SubTable.ParentID = #Test2.PK
and SubTable.CulCount is null)

blindman