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