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

cost estimate

Hi, all
I am writing a midware for some heterogeneous data sources, and I need to do some cost estimation to some SQL queries before they are sent to an underlying SQL server.
My question is, is it possible to get the cost estimates of queries from SQL server by some API ? If not, can I get the numbers of distinct values for attributes from the system catalogs ? and how about other statistics, such as cardinality, selectivity?

Thanks a lot!Use QUERY EXECUTION PLAN from Query analyzer and also PROFILER to assess the query process time and indexes.|||Originally posted by Satya
Use QUERY EXECUTION PLAN from Query analyzer
Yes, I can see the execution plan graphiclly displayed in Query analyzer.
But is there a way to use that information in my application?
BTW, what is PROFILER?|||use:

set showplan_all on

it will return what I think you're looking for, in table-based format, which you can iterate through and do what you wish with.|||Thanks strader.

PROFILER is tool used to trace the events on SQL Server, which is best used to know slow running queries and process.

Refer to books online for more information.|||Originally posted by Satya
Thanks strader.

PROFILER is tool used to trace the events on SQL Server, which is best used to know slow running queries and process.

Refer to books online for more information.

Thanks a lot!