SQL Server UDF used to read all levels of subnodes under any node in the tree

SRC: http://blog.joycode.com/mmkk/archive/2004/05/13/21428.aspx

Tree structure is a common data structure in applications. The simplest design is similar:
ID, name, parent_id

This simplest design usually needs to combine recursion to form a tree UI. Moreover, it is not very convenient for obtaining all the lower-level nodes of a node,
To simplify this operation, use the following UDF:
-- Obtain all lower-level nodes of the current node in the Tree Structure

Create   Function   [ DBO ] . [ Getchildcategories ] (@ Parent_id Int )
Returns @ Work Table (Num Int   Identity ( 1 , 1 ), Category_id Int ) As
Begin  
Declare @ Childrencount Int , @ Currcategory_id Int , @ Num Int
Set @ Num =   1
Insert @ Work
Select Category_id
From Category_classification
Where Parent_id = @ Parent_id
Set @ Childrencount = @ Rowcount
While (@ Num <= @ Childrencount)
Begin
Select   Top   1 @ Currcategory_id = Category_id
From @ Work
Where Num = @ Num

Insert @ Work
Select Category_id
From Category_classification
Where Parent_id = @ Currcategory_id

Set @ Childrencount = @ Childrencount + @ Rowcount
Set @ Num = @ Num +   1
End
Return
End

 

This is mainly to convert a recursive operation into a single cyclic operation.CodeI don't know how to write comments,
There is no big data test.