Query categories and implement SQL stored procedures for classification statistics

Problem description:

SQL stored procedures for querying and classifying statistic data infinitely. The detailed data table is designed as follows:

Designed data table
Table 1: pub_dict-used to store a category table. Theoretically, it can store an unlimited number of levels. Currently, it has three levels.
Dictid -- number
Dictname -- name
Parentid -- parent class number (represented by 0 at the root level)

In another table, pub_info references the preceding dictid number as the category information.
The table structure is roughly as follows:
Infoid -- Automatic ID
Infotitle -- title
Infodate -- Time
Infocontent -- Content
Dictid --- Reference Category Number

The classification of the table pub_info may contain classification data of level 1 (for example, 101), level 2 (for example, 101001), and level 3 (101001001 ).
Now we need to calculate the quantity of each category hierarchically.ProgramThe category name, number, and statistical result are displayed on the page.
In addition, note that the statistical data of the parent class should contain the number of sub-classes.

For example, level 1 should include level 2 and level 3, and level 2 should include Level 3.
The statistical results of 101 categories also contain the results of all subcategories (such as 101003 ...)

Note: Due to server platform problems, the database is SQL2000

The implementation method is as follows:

 
/*************************************** * ************************************ To obtain various product Category ID, name and number of statistics
 

 
* Creation Time:
 

* @ RID: equivalent to the parent class number (level 1 is 0) * @ typecode varchar (10), -- subcategory Code * @ Tblname: Table name * @ idname: name of the category number field referenced in the information table, for example, the pub_info Table reference is (res_id) * @ strwhere: adds a filter condition string without the keyword where, for example, state = 2. Multiple combinations are combined with and for calling example: exec usp_pub_getpurveyrestypecount 30102, 't', 'pub _ info', 'res _ id ', 'State = 2 and area_id = 10501 '******************************* **************************************** * ************/create proc usp_pub_getrestypecount (@ rid int, -- category number indicates the parent class @ typecode varchar (10), -- subcategory code @ tblname varchar (50), -- table name @ idname varchar (50 ), -- ID column name @ strwhere varchar (200) -- filter condition) asbegin -- create a temporary table create table # TT ([dictid] int primary key, [dictname] varchar (50 ), [total] INT) Declare @ I int, -- index number @ tcount int, -- statistic data @ Max int, -- maximum classification number @ min int, -- Minimum Classification Number @ Len int, -- length of the number @ dname varchar (50), -- category name @ SQL nvarchar (1000), -- Query SQL statement @ sublen INTIF (@ typecode = 'T ') set @ sublen = 2 -- calculate the length, because some are: 10101001, and some are else set @ sublen = 3 set @ Len = Len (@ RID) in the form of 1010010001) set @ min = (select Min (dictid) from pub_dict where parentid = @ RID and typecode = @ typecode) set @ max = (select max (dictid) from pub_dict where parentid = @ RID and typecode = @ typecode) set @ I = @ min while (@ I <= @ max) begin set @ SQL = 'select @ tcount = count (1) from '+ @ tblname + 'where substring (cast (' + @ idname + 'As varchar (20 )), 1. Cast ('+ Cast (@ Len as varchar (10) + 'as INT) =' + Cast (@ I as varchar (20 )) + 'and' + @ strwhere execute sp_executesql @ SQL, n' @ tcount int output', @ tcount output set @ dname = (select dictname from pub_dict where dictid = @ I) insert into # TT values (@ I, @ dname, @ tcount) set @ I = @ I + 1 endselect * from # TT -- Query output result drop table # TT -- delete temporary table endgo

Although this method can be used to flexibly implement this function, it is worth considering to create a temporary table and delete a temporary table every call. When the data volume is large, performance is a problem. However, we have never thought of a better solution so far. We look forward to your solutions.