sql server 部门递归函数

create function [dbo].[ft_get_recursion_deptlist](  
@deptcode nvarchar(20),--部门代码  
@direction bit =0 --递归方向 0表示向上，1表示向下 
--@showDelete bit =0--0表示不显示删除部门,1表示显示删除部门
) 

returns @result table(DeptName nvarchar(50),DeptCode nvarchar(30),ParentDeptCode nvarchar(30),DeptCategoryCode nvarchar(20),
  DeptCategory nvarchar(50),id int ,CostCode nvarchar(30) , ProfitCode  nvarchar(30),managerEmpCode nvarchar(10),managerJobCode nvarchar(20),
  managerPostCode nvarchar(20),deptpath nvarchar(500),IsProdDept bit ,IsOrderProdDept bit,IsIndependent varchar(1),
  BUCode nvarchar(30),Companyid nvarchar(10),DeleteFlag varchar(1))
as  

  begin  
    if(@direction=0)  
    begin   

      --向上递归，获取指定部门向上的所有部门  
      WITH deptlist   
      AS  
      (  
          SELECT a.deptName,a.deptcode,a.parentdeptcode,a.DeptCategoryCode,a.DeptCategory,a.id, a.CostCode,a.ProfitCode ,
          a.Manager_EmployeeCode,a.Manager_JobCode,a.Manager_PositionCode, cast(a.deptcode as nvarchar(4000)) AS PATH,  
          a.IsProdDept ,a.IsOrderProdDept,isnull(a.IsIndependent,'N') as IsIndependent,
          case when a.DeptCategoryCode='BU' then a.deptcode else isnull((select bucode from ft_get_bucode(a.deptcode)),a.Companyid) end as BUCode ,
          a.Companyid,isnull(a.DeleteFlag ,'N') as DeleteFlag
          FROM sys_company_dept a  
          WHERE a.deptcode = @deptcode  and  isnull(a.deleteflag,'N')!='Y'

          UNION ALL  

          SELECT b.deptName,b.deptcode,b.parentdeptcode,b.DeptCategoryCode,b.DeptCategory,b.id, b.CostCode,b.ProfitCode ,
          b.Manager_EmployeeCode,b.Manager_JobCode,b.Manager_PositionCode,d.PATH+'->'+Cast(b.deptcode as nvarchar(4000)) PATH ,   
          b.IsProdDept ,b.IsOrderProdDept,isnull(b.IsIndependent,'N') as IsIndependent,
          case when b.DeptCategoryCode='BU' then b.deptcode else isnull((select bucode from ft_get_bucode(b.deptcode)),d.Companyid) end as BUCode,
          b.Companyid,isnull(b.DeleteFlag ,'N') as DeleteFlag
          FROM sys_company_dept b  
          INNER JOIN deptlist d ON d.parentdeptcode=b.deptcode  and isnull(b.deleteflag,'N')!='Y'--向上递归  
      )  

      insert into @result  
      SELECT * FROM deptlist  where isnull(deleteflag,'N')!='Y'

      --限制递归次数  
      OPTION(MAXRECURSION 8)  

 end  
 else  
 begin  
     --向下递归，获取指定部门向下的所有部门
      WITH deptlist   
      AS  
      (  
          SELECT a.deptName,a.deptcode,a.parentdeptcode,a.DeptCategoryCode,a.DeptCategory,a.id, a.CostCode,a.ProfitCode ,  
          a.Manager_EmployeeCode,a.Manager_JobCode,a.Manager_PositionCode,Cast(a.deptcode as nvarchar(4000)) AS PATH,  
          a.IsProdDept ,a.IsOrderProdDept,isnull(a.IsIndependent,'N') as IsIndependent,
          case when a.DeptCategoryCode='BU' then a.deptcode else isnull((select bucode from ft_get_bucode(a.deptcode)),a.Companyid) end as BUCode,
          a.Companyid,isnull(a.DeleteFlag ,'N') as DeleteFlag 
          FROM sys_company_dept a  
          WHERE a.deptcode = @deptcode  and isnull(a.deleteflag,'N')!='Y'

          UNION ALL  

          SELECT b.deptName,b.deptcode,b.parentdeptcode,b.DeptCategoryCode,b.DeptCategory,b.id, b.CostCode,b.ProfitCode ,  
          b.Manager_EmployeeCode,b.Manager_JobCode,b.Manager_PositionCode,d.PATH+'->'+Cast(b.deptcode as nvarchar(4000)) PATH,  
          b.IsProdDept ,b.IsOrderProdDept,isnull(b.IsIndependent,'N') as IsIndependent, 
          case when b.DeptCategoryCode='BU' then b.deptcode else isnull((select bucode from ft_get_bucode(b.deptcode)),b.Companyid) end as BUCode,
          b.Companyid ,isnull(b.DeleteFlag ,'N') as DeleteFlag
          FROM sys_company_dept b  
          INNER JOIN deptlist d ON d.deptcode=b.parentdeptcode and isnull(b.deleteflag,'N')!='Y'--向下递归  
      )  
      insert into @result  
      SELECT * FROM deptlist where isnull(deleteflag,'N')!='Y'

      --限制递归次数  
      OPTION(MAXRECURSION 8)  
 end  

 return;  

end