Use proc for tree Recursion

/*** // ****** Object: Table [DBO]. [tree] script Date: 18:07:00 ******/
Create Table [DBO]. [tree] (
[ID] [int] identity (1, 1) not null,
[Pid] [int] Null,
[Name] [varchar] (10) Collate chinese_prc_ci_as null
) On [primary]
Go
 
Create clustered index [ix_tree] on [DBO]. [tree] ([pid]) on [primary]
Go

Alter table [DBO]. [tree] With nocheck add
Constraint [pk_tree] primary key nonclustered
(
[ID]
) On [primary],
Constraint [subid cannot be equal to parent id] Check ([ID] <> [pid])
Go

Alter table [DBO]. [tree] add
Constraint [fk_tree_tree] foreign key
(
[Pid]
) References [DBO]. [tree] (
[ID]
)
Go

/** // ****** Object: User-Defined Function DBO. fgettreetable script Date: 18:07:02 ******/
Create Function DBO. fgettreetable
(
@ ID Int = NULL
)
Returns @ tab table (ID int, PID int, name varchar (10), levint)
As
Begin
Declare @ levint
Set @ lev= 0

While @ lev= 0 or @ rowcount> 0
Begin
Set @ lev= @ lev+ 1
Insert @ tab (ID, PID, name, column)
Select ID, PID, name, @ levfrom tree where (@ lev= 1 and (pid = @ ID) or (@ ID is null and PID is null ))) or (PID in (select ID from @ tab where lev= @ Lev-1 ))
Order by ID
End
Return
End

Go

-- actual data
insert tree (PID, [name]) values (null, 'Company ')
insert tree (PID, [name]) values (3, 'it')
insert tree (PID, [name]) values (1, 'fin ')
insert tree (PID, [name]) values (4, 'xz ')
insert tree (PID, [name]) values (1, 'hr')
insert tree (PID, [name]) values (6, 'hr')
go
select * from tree
Delete from tree
select * From DBO. fgettreetable (null)