递归返回所有子部门id

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

 

-- =============================================

-- Author: <Liup>

-- Create date: <09-5-6>

-- Description: <递归调用，返回所有子部门，包括自己>

-- =============================================

ALTER FUNCTION [dbo].[Fn_GetDepID]  

(

@DepID VARCHAR(50) --节点ID

)

RETURNS @reTable TABLE

(DepteID VARCHAR(50) --部门ID

) --返回table类型

AS

BEGIN

insert into @reTable SELECT deptid FROM department where deptid=@DepID and deptdelflag<>1 

WHILE @@Rowcount > 0

BEGIN

insert into @reTable select  deptid from department as a inner join @reTable as b 

on a.deptparentid = b.DepteID and a.deptid not in(select DepteID from @reTable)

END

RETURN

END