MSSQL traverses all tree node functions

 

// Function Definition

// When debugging the MSSQL function, you must enable the "query analysis tool"-"query"-"Display Server trace" option.

 

Create function f_Cid ()

RETURNS @ t_Level TABLE (LPARENTID bigint, LID bigint, Level int) // defines the structure of the returned table

AS

 

BEGIN

 

DECLARE @ Level int // originally used as the node Level, which is mainly used to associate Parent and Child Nodes

SET @ Level = 1

 

DECLARE id_cursor cursor for // retrieve the node ID
SELECT lid
FROM Tb000000department

 

DECLARE @ my_Id bigint // temporary variable for storing the node ID

 

OPEN id_cursor // OPEN the cursor and traverse all node IDS

 

Fetch next from id_cursor INTO @ my_id // There is a pair of fetch next statements in the fetch next & WHILE LOOP

WHILE @ FETCH_STATUS = 0

BEGIN

INSERT @ t_Level SELECT @ my_id, a. LID, @ Level FROM tb1_00department a where a. LID = @ my_id

WHILE @ ROWCOUNT> 0

BEGIN

SET @ Level = @ Level + 1

INSERT @ t_Level SELECT @ my_id, a. LID, @ Level

FROM tb1_00department a, @ t_Level B

WHERE a. LPARENTID = B. lid and B. Level = @ Level-1/B. Level = @ Level-1 Ensure that the query can only be performed simultaneously with the current node.

END

Fetch next from id_cursor INTO @ my_id

END

 

CLOSE id_cursor // CLOSE and delete the cursor
DEALLOCATE id_cursor

 

RETURN

END

 

 

 

 

// Function call

 

Select C. * from f_Cid () C