--建立測試環境
Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--建立函數
Create Function F_GetParent(@departmenid Int)
Returns @Tree Table (departmenid Int, parentid Int)
As
Begin
Insert @Tree Select * From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not Null
Return
End
GO
--測試
Select departmenid From dbo.F_GetParent(8) Order By parentid
GO
--刪除測試環境
Drop Table department
Drop Function F_GetParent
--結果
/*
departmenid
1
3
7
8
*/

--建立測試環境
Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--創建存儲過程
Create ProceDure SP_GetParent(@departmenid Int)
As
Begin
Select * Into #Tree From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert #Tree Select A.* From department A Inner Join #Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From #Tree) Where A.parentid Is Not Null
Select departmenid From #Tree Order By parentid
Drop Table #Tree
End
GO
--測試
EXEC SP_GetParent 8
GO
--刪除測試環境
Drop Table department
Drop ProceDure SP_GetParent
--結果
/*
departmenid
1
3
7
8
*/