【SQL SERVER】广度遍历科室树表的存储过程设计

深度遍历经常由于数据多，而SQL SERVER无法超过32层递归（嵌套）[错误提示：超出了存储过程、函数、触发器或视图的最大嵌套层数（最大层数为 32）]调用的关系，优化改为广度遍历证实有效。
代码如下：
1、方法一：

ALTER PROCEDURE [dbo].[pGetDescendedDepartments](
    @i_departmentIds varchar(4000), --ID串用英文逗号(,)分隔
    @i_level tinyint = 0
)
AS
set nocount on

DECLARE @departmentId int 
BEGIN TRY
    declare @s nvarchar(4000);
    IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##Department')
    begin
        CREATE TABLE ##Department( [ID] [int] identity(1,1) NOT NULL ,
            [DepartmentId] [int] ,
            [DepartmentName] [nvarchar](50) NOT NULL ,
            [MnemonicCode] [nvarchar](10) NOT NULL ,
            [ParentDepartmentId] [int] NOT NULL ,
            [IdleFlag] [tinyint] NOT NULL ,
            [DepartmentNo] [nvarchar](10) NOT NULL ,
            [VirtualFlag] [tinyint] NOT NULL,
            [LevelFlag] [tinyint] NOT NULL default 0
        );
    end
    
    SET @i_level = @i_level + 1;
    INSERT ##Department(DepartmentId, DepartmentName, MnemonicCode, ParentDepartmentId, IdleFlag, VirtualFlag, DepartmentNo, LevelFlag)
    SELECT DepartmentId, DepartmentName, MnemonicCode, ParentDepartmentId, IdleFlag, VirtualFlag, DepartmentNo, @i_level
    FROM dbo.tbDepartment with(nolock) WHERE DepartmentId > -1 AND ParentDepartmentId in (SELECT CAST(Col AS INT) ID FROM [dbo].[fnStrSplit](@i_departmentIds, ','));
    --select * from ##Department;
    PRINT '@@RowCount ：' + CAST(@@rowcount AS VARCHAR(128));
 
    set @s=''; 
    SELECT @s = @s + ',' + convert(varchar(10), DepartmentId) FROM ##Department WHERE LevelFlag = @i_level ORDER BY ID;
    SET @s = substring(@s, 2, len(@s));
    PRINT '@s ：' + @s;
    
    if (len(@s) > 0) 
        exec [dbo].[pGetDescendedDepartments] @s, @i_level
    else
    begin
        SELECT * FROM ##Department with(nolock) ORDER BY LevelFlag, ParentDepartmentId, DepartmentId;
        DROP TABLE ##Department;
    end
    
END TRY
BEGIN CATCH
    PRINT 'ErrorMessage ：' + ERROR_MESSAGE();
    --SELECT * FROM ##Department with(nolock) 
    IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##Department')
        DROP TABLE ##Department
END CATCH

set nocount off

2、方法二：

ALTER PROCEDURE [dbo].[pGetDescendedDepartments](
    @i_departmentIds varchar(4000) --ID串用英文逗号(,)分隔
)
AS
set nocount on

DECLARE @departmentId int 
BEGIN TRY
    declare @s nvarchar(4000);
    IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##Department')
    begin
        CREATE TABLE ##Department( [ID] [int] identity(1,1) NOT NULL ,
            [DepartmentId] [int] ,
            [DepartmentName] [nvarchar](50) NOT NULL ,
            [MnemonicCode] [nvarchar](10) NOT NULL ,
            [ParentDepartmentId] [int] NOT NULL ,
            [IdleFlag] [tinyint] NOT NULL ,
            [DepartmentNo] [nvarchar](10) NOT NULL ,
            [VirtualFlag] [tinyint],
            [Handled] [bit] NOT NULL default 0
        );
    end

    INSERT ##Department(DepartmentId, DepartmentName, MnemonicCode, ParentDepartmentId, IdleFlag, VirtualFlag, DepartmentNo)
    SELECT DepartmentId, DepartmentName, MnemonicCode, ParentDepartmentId, IdleFlag, VirtualFlag, DepartmentNo 
    FROM dbo.tbDepartment with(nolock) WHERE DepartmentId > -1 AND ParentDepartmentId in (SELECT CAST(Col AS INT) ID FROM [dbo].[fnStrSplit](@i_departmentIds, ','));
    --select * from ##Department;
    PRINT '@@RowCount ：' + CAST(@@rowcount AS VARCHAR(128));
 
    set @s=''; 
    select @s = @s + ',' + convert(varchar(10), DepartmentId) from dbo.tbDepartment with(nolock) WHERE DepartmentId > -1 AND ParentDepartmentId in (SELECT CAST(Col AS INT) ID FROM [dbo].[fnStrSplit](@i_departmentIds, ','))
    SET @s = substring(@s, 2, len(@s));
    PRINT '@s ：' + @s;
    
    if (len(@s) > 0) 
        exec [dbo].[pGetDescendedDepartments] @s
    else
    begin
        SELECT * FROM ##Department with(nolock) ORDER BY ParentDepartmentId, DepartmentId;
        DROP TABLE ##Department;
    end
    
END TRY
BEGIN CATCH
    PRINT 'ErrorMessage ：' + ERROR_MESSAGE();
    --SELECT * FROM ##Department with(nolock) 
    IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = '##Department')
        DROP TABLE ##Department
END CATCH

set nocount off

3、辅助自定义函数  [dbo].[fnStrSplit] 的代码如下：

ALTER FUNCTION [dbo].[fnStrSplit](@string NVARCHAR(max), @split VARCHAR(2))
RETURNS @tmp TABLE(Col VARCHAR(max))
AS 
BEGIN
WHILE (CHARINDEX(@split,@string) <> 0)
	BEGIN 
		INSERT INTO @tmp( Col ) VALUES (SUBSTRING(@string,1,CHARINDEX(@split,@string)-1))
		SET @string = STUFF(@string,1,CHARINDEX(@split,@string),'')
	END 
	INSERT INTO @tmp( Col ) VALUES (@string)
	RETURN 
END

4、另一种思路，用公用表表达式(common table expression，CTE)递归查询

CTE是定义在SELECT、INSERT、UPDATE或DELETE语句中的临时命名的结果集，CTE也可以用在视图的定义中。在CTE中可以包括对自身的引用，因此这种表达式也被称为递归CTE。
在TSQL脚本中，也能实现递归查询，SQLServer提供CTE(Common Table Expression)，只需要编写少量的代码，就能实现递归查询，本文详细介绍CTE递归调用的特性和使用示例，递归查询主要用于层次结构的查询，从叶级(LeafLevel)向顶层(Root Level)查询，或从顶层向叶级查询，或递归的路径(Path)。

递归查询原理：
CTE的递归查询必须满足三个条件:初始条件，递归调用表达式，终止条件，CTE递归查询的伪代码如下：
WITH cte_name ( column_name [,...n] )
AS
(
--Anchor member is defined
CTE_query_definition
UNION ALL
--Recursive member is defined referencing cte_name
CTE_query_definition
)
-- Statement using the CTE
SELECT *
FROM cte_name

（1）递归查询至少包含两个子查询：

• 第一个子查询称作定点(Anchor)子查询；定点查询只是一个返回有效表的查询，用于设置递归的初始值；
• 第二个子查询称作递归子查询；该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；
• 两个子查询使用union all，求并集；

（2）CTE的递归终止条件

• 递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。

默认的递归查询次数是100，可以使用查询提示(hint)：MAXRECURSION控制递归的最大次数：OPTION(MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示:option(maxrecursion 0)；
;with cte as
(
select UserlD, ManagerlD, name, name as ManagerName from dbo.dt_user where ManagerlD = -1
union all
select c.UserlD, c.ManagerlD, c.Name, p.name as ManagerName from cte p
inner join dbo.dt_user c on p.UserlD = c.ManagerlD
)
select UserlD, ManagerlD, Name, ManagerName from cte
order by UserlD
option(maxrecursian 10)

当递归查询达到指定或默认的MAXRECURSION数量限制时，SQL Server将结束查询并返回错误，如下:
The statement terminated The maximum recursion 10 has heen exhausted hefore statement comnletion
事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过where条件限制递归的次数。

（3）递归步骤
step1: 定点子查询设置CTE的初始值，即CTE的初始值SetO递归调用的子查询过程；递归子查询调用递归子查询；
step2: 递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set，第一次执行递归子查询之后，CTE名称是指结果集Set1；
step3: 递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2;
step4: 在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；
Step5: 如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归。

具体应用代码如下：

CREATE PROCEDURE [dbo].[pGetDepartmentAndChildren](
    @i_departmentId INT
)
AS
set nocount on

declare @False as Bit = 0, @True as Bit = 1;

with Children as (
  select d.ParentDepartmentId PID, d.DepartmentId ID,
    Convert( VarChar(4096), '>' + Convert( VarChar(10), d.DepartmentId ) + '>' ) as Path, @False as Loop, d.*
    from tbDepartment d
    where d.DepartmentId = @i_departmentId
  union all
  select child.ParentDepartmentId PID, child.DepartmentId ID, 
    Convert( VarChar(4096), Path + Convert( VarChar(10), child.DepartmentId ) + '>' ),
    case when CharIndex( '>' + Convert( VarChar(10), child.DepartmentId ) + '>', Path ) = 0 then @False else @True end, child.*
    from tbDepartment as child inner join
      Children as parent on parent.DepartmentId = child.ParentDepartmentId
    where parent.Loop = 0 )
  select distinct *
    from Children
    option ( MaxRecursion 0 );

set nocount off

查询所有父子科室信息：

WITH cte( ParentDepartmentId, DepartmentId, Path, Loop, Level ) AS (
    SELECT ParentDepartmentId, DepartmentId
    , Convert( VarChar(4096), '>' + Convert( VarChar(10), DepartmentId ) + '>' ) as Path, 0 as Loop, 0 as Level
    FROM dbo.tbDepartment WITH(NOLOCK)
    WHERE ParentDepartmentId = -1
    UNION ALL
    SELECT d.ParentDepartmentId, d.DepartmentId
    , Convert( VarChar(4096), Path + Convert( VarChar(10), d.DepartmentId ) + '>' ) as Path
    , case when CharIndex( '>' + Convert( VarChar(10), d.DepartmentId ) + '>', Path ) = 0 then 0 else 1 end, Level + 1 Level
    FROM tbDepartment d WITH(NOLOCK)
    INNER JOIN cte
    ON d.ParentDepartmentId = cte.DepartmentId
    WHERE cte.Loop = 0
)
SELECT distinct * 
FROM cte WHERE Path like '>-1>%' and Path != '>-1>'
OPTION ( MaxRecursion 0 );