SQL实现递归算法获取部门所有子部门

-- 根据用户编号、企业编号、数据等级获取部门列表

--

CREATE PROC TDept_GetList_ByUidAndComNoAndLevel

@Uid INT,

@ComNo INT,

@DataLevel INT

AS

DECLARE @detptId INT

SELECT @detptId=ISNULL(DepartmentID,0) FROM dbo.ChildUser WHERE  id=@Uid

--PRINT @detptId

IF(@DataLevel=0)--查个人

SELECT * FROM dbo.TDept WHERE TDtID=@detptId

ELSE IF(@DataLevel=2)--查全部

SELECT * FROM dbo.TDept WHERE LTRIM(RTRIM(TDtComNo))=@ComNo

ELSE IF(@DataLevel=1)

BEGIN  

DECLARE @t_Level TABLE(TDtID INT NULL,TDtPID int null,TDtName VARCHAR(50),TDtSort INT NULL ,TDtFlag INT NULL,TDtComNo VARCHAR(100),level int null)

DECLARE @Level int

SET @Level=0

INSERT @t_Level SELECT [TDtID],[TDtPID],[TDtName],[TDtSort],[TDtFlag],[TDtComNo],@level 

FROM [TrackWay_Extend].[dbo].[TDept] where TDtID=@detptId 

--FROM @t

--WHERE PID IS NULL

WHILE @@ROWCOUNT>0

BEGIN

SET @Level=@Level+1

INSERT @t_Level SELECT a.[TDtID],a.[TDtPID],a.[TDtName],a.[TDtSort],a.[TDtFlag],a.[TDtComNo],@level 

FROM [TrackWay_Extend].[dbo].[TDept] a,@t_Level b

WHERE a.TDtPID=b.TDtID

AND b.Level=@Level-1

END

SELECT * FROM @t_Level

END 

ELSE

SELECT * FROM dbo.TDept WHERE TDtID=@detptId