(Original) based on the tree structure data stored in the chain of the relational database system, calculate the algorithm of all the nodes of the subtree under a node (t-SQL language implementation)

(Original) based on the tree structure data stored in the chain of the relational database system, calculate the algorithm of all the nodes of the subtree under a node (t-SQL language implementation) 

Create Function f_subtree (@ dpcode varchar (9)/* tree structure data, which is used to find the User-Defined Function of all the nodes of the subtree under a node */
Returns @ new table ([dpcode] [varchar] (9), -- Node Code
[Dpname] [varchar] (20), -- node name
[Dpcode_p] [varchar] (9) -- parent node code of this node
)
Begin
Declare @ temp table ([dpcode] [varchar] (9), -- Node Code
[Dpname] [varchar] (20), -- node name
[Dpcode_p] [varchar] (9) -- parent node code of this node
) -- Temporary data storage table
Declare @ t table ([dpcode] [varchar] (9), -- Node Code
[Dpname] [varchar] (20), -- node name
[Dpcode_p] [varchar] (9) -- parent node code of this node
) -- Intermediate temporary table
Declare @ TT table ([dpcode] [varchar] (9), -- node Encoding
[Dpname] [varchar] (20), -- node name
[Dpcode_p] [varchar] (9) -- parent node code of this node
) -- Intermediate exchange temporary table

Delete @ temp
Delete @ t
Delete @ TT
Delete @ new

Insert into @ temp select * From dpet
Insert into @ t select * From @ temp where dpcode_p = @ dpcode
Insert into @ new select * From @ t -- result temporary table
While (exists (select * From @ t where dpcode in (select dpcode_p from @ temp )))
-- The loop is stopped only when all nodes on a layer are leaves.
Begin
Insert into @ TT select * From @ t -- intermediate exchange temporary table
Delete @ t
Insert into @ t select * From @ temp where dpcode_p in
(Select dpcode from @ TT)
Delete @ TT
Insert into @ new select * From @ t
End
Insert into @ new select * From @ temp where dpcode = @ dpcode

Return
End

------------------------------------------------- (The following is the implementation of the stored procedure)

Create Table dpet (-- Data Storage table structure of tree structure data
[Dpcode] [varchar] (9), -- Node Code
[Dpname] [varchar] (20), -- node name
[Dpcode_p] [varchar] (9) -- parent node code of this node
) On [primary]

/* Ms SQL 2000 is debugged. The structure of the table temp, T, new, and TT is consistent with that of the table dpet */

Create procedure desc_dept (@ dpcode varchar (9)
-- Tree structure data, finding all the nodes of the subtree under a node
Truncate table temp
Truncate table t
Truncate table TT
Truncate table new
Insert into temp select * From dpet -- temporary data storage table
Insert into T select * from temp where dpcode_p = @ dpcode -- intermediate temporary table
Insert into new select * from t -- result temporary table
While (exists (select * from t where dpcode in (select dpcode_p from temp )))
-- The loop is stopped only when all nodes on a layer are leaves.
Begin
Insert into TT select * from t -- intermediate exchange temporary table
Truncate table t
Insert into T select * from temp where dpcode_p in
(Select dpcode from TT)
Truncate table TT
Insert into new select * from t
End
Insert into new select * from temp where dpcode = @ dpcode
Go