Typical Stored Procedure Problems

Involves a table: xkb_treenode

The table structure is as follows:
Node_id int // node ID
Parentnode_id int // parent node ID
Node_text varchar // node content
Ismodule bit // whether it is a leaf node

Currently, the stored data includes:

Node_id parentnode_id node_text ismodule
1-1 Language and Literature 0
2-1 mathematics 0
3-1 Technology 0
4 1 Language 0
5 1 Foreign Language 0
6 5 English 0
7 6 junior high school English 0
8 7 Testa 1
9 4 Determination is 2 1
10 2 Test 3 1

Now the problem is:
Can I create a stored procedure,
Based on the value of the ismodule field in the table (1 indicates the final leaf node ),
For example, "tsta" is a leaf node, and the ancestor node of "tsta" is found layer by layer:
Testa-> junior high school English-> Foreign Language-> Language and Literature
That is to say, "tsta" is used to find "Language and Literature ".

The final returned form is:
Leaf node ID parent node ID node name ancestor node ID
8 7 Testa Language and Literature 1
9 4 Determination is 2 Language and Literature 1
10 2 Test 3 math 2

 

//////////////////////////////////////// /////////////////////////////////
Correct answer:

-- Generate Test Data
Create Table xkb_treenode (
Node_id int,
Parentnode_id int,
Node_textvarchar (10 ),
Ismodulebit)

Insert into xkb_treenode select 1,-1, 'language and literature ', 0
Insert into xkb_treenode select 2,-1, 'mat', 0
Insert into xkb_treenode select 3,-1, 'techno', 0
Insert into xkb_treenode select 4, 1, 'China', 0
Insert into xkb_treenode select 5, 1, '', 0
Insert into xkb_treenode select 6, 5, 'English ', 0
Insert into xkb_treenode select 7, 6, 'Junior English ', 0
Insert into xkb_treenode Select 8, 7, 'insta', 1
Insert into xkb_treenode select 9, 4, 'Measurement is 2', 1
Insert into xkb_treenode select 10, 2, 'test 3', 1

-- Create a stored procedure
Create procedure sp_test
As
Begin
Select
A. node_id,
A. parentnode_id,
A. node_text,
B. node_id as ancestor_id,
B. node_text as ancestor_text
Into
# T
From
Xkb_treenode A, xkb_treenode B
Where
A. parentnode_id = B. node_id and A. ismodule = 1

While (exists (select 1 from xkb_treenode A, # t B where a. node_id = ancestor_id and A. parentnode_id! =-1 ))
Begin
Update # T
Set
Ancestor_id = B. p_id,
Ancestor_text = B. p_text
From
# T,
(Select
C. node_id,
D. node_id as p_id,
D. node_text as p_text
From
Xkb_treenode C, xkb_treenode d
Where
C. parentnode_id = D. node_id) B
Where
A. ancestor_id = B. node_id
End

Select * from # T order by node_id
End

-- Execute the stored procedure and check the result.
Exec sp_test