SQL server implements the stored procedure of tree structure data query, similar to the connect

Create procedure [DBO]. [sp_get_tree_relation]
(@ Table_name nvarchar (50), @ ID nvarchar (50), @ name nvarchar (50), @ parent_id nvarchar (50), @ startid nvarchar (20 ))
As
Declare @ v_id int
Declare @ v_level int
Declare @ SQL nvarchar (500)
Begin
Create Table # temp (ID nvarchar (20), name nvarchar (50), parent_id nvarchar (20 ))
Create Table # T1 (ID nvarchar (20), name nvarchar (50), parent_id nvarchar (20), Level INT)
-- Pilot the data of the table to be queried to the temporary table
Set @ SQL = 'insert into # temp select' + @ ID + ',' + @ name + ',' + @ parent_id + 'from' + @ table_name
Exec sp_executesql @ SQL

Set @ v_level = 1
Set @ v_id = @ startid
Insert # T1 select a. ID, A. Name, A. parent_id, @ v_level from # temp A where a. ID = @ v_id
While @ rowcount> 0
Begin
Set @ v_level = @ v_level + 1
Insert # T1 select a. ID, A. Name, A. parent_id, @ v_level
From # temp A where a. parent_id in
(Select ID from # T1 where level = @ v_level-1)
End

Select a. Level, A. ID, A. Name, A. parent_id, B. Name parent_name
From # T1 a left Outer Join # temp B
On a. parent_id = B. ID order by A. Level ASC
End

-- Execute
If the table organize is a hierarchical table, run the stored procedure to obtain the tree results of the organizational relationship.
Sp_get_tree_relation 'orgnaize ', 'org _ id', 'org _ name', 'parent _ org', 1