A question and answer: Stored procedure classic problem

Stored Procedures | issues
Only one table is involved: Xkb_treenode

The table structure is like this:
node_id int//Node ID
parentnode_id int//parent Node ID
Node_text varchar//node content
IsModule bit//whether leaf node

The data that is now saved is:

node_id parentnode_id Node_text IsModule
1-1 Languages and Literature 0
2-1 Mathematics 0
3-1 Technology 0
4 1 Languages 0
5 1 Foreign Languages 0
6 5 English 0
7 6 Junior English 0
8 7 Tesla Tower 1
9 4 Determination is 2 1
10 2 Test 3 1


Now the question is:
Can you do this by doing a stored procedure,
Depending on the value of the IsModule field in the table (a value of 1 indicates the final leaf node),
For example, "Tesla Tower" as a leaf node, layers upward to find the "tower" ancestor node:
Tesla-〉 Junior English-〉 English-〉 foreign Language-〉 languages and literature
To find "language and literature" through "The Tower"

The final return form is:
Leaf Node ID parent node ID node name ancestor node name ancestor Node ID
8 7 Tower Language and Literature 1
9 4 determinations are 2 languages with 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, ' math ', 0
Insert INTO Xkb_treenode Select 3,-1, ' technology ', 0
INSERT INTO Xkb_treenode Select 4, 1, ' languages ', 0
INSERT INTO Xkb_treenode Select 5, 1, ' foreign language ', 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, ' Tesla Tower ', 1
INSERT INTO Xkb_treenode Select 9, 4, ' determination 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 A,
(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


--Execution of stored procedures, the results of the landlord himself to see
EXEC sp_test