Descendent Count for a Directory

Need routine for counting the number of descendents in a directory. For example this application is a ASP.NET application written in VB.NET, it has categories containing items, each category can have child categories containing items. I need each category to have a count included of all descendents. This count needs to be dynamic, but optimized in order not to kill the back end database. It cannot use SQL User Defined Functions (due to installation problems), but can use stored procedures if needed. The count can either be contained within a count field in the table for the categories, but it is preferred that is dynamically generated for each request in order to be accurate. It is preferred that you create the TSQL procedure, and I will provide you with the table structure for creating the sproc. So the final directory listings would look like so: Category(100) --- contains culmative count of children and all descendents. Child Category 1(50) 25 listings Child Child Cat 1 (25) 25 listings Child Category 2(50) 50 listings ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform Must be done either via [login to view URL] in VB.NET or preferred to be a TSQL statement using sprocs (no user defined functions). TSQL statement would return a field with the culmative count for each category.