更新层级和层级编码

之前文章提到过（SQL递归-题外话）如果是树形我们除了递归可以用层级和层级编码来查询，这样我们能更便捷的查找树形，今天我要介绍的是新增的时候传入当前级ID，然后调用该存储过程自动更新该级及其下面的所有子级的层级和层级编码。话不多说，见下存储过程：

 

/*----------------------------------------------------------*/
/*    [PC1LevelCodeSet]                                  */
/*----------------------------------------------------------*/
IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[PC1LevelCodeSet]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
DROP PROCEDURE [PC1LevelCodeSet]
GO
CREATE PROC [PC1LevelCodeSet] 
(
@idColunmName nvarchar(100),--ID字段名称
@idValue varchar(36) ,--ID字段值
@levelColumnName nvarchar(100),--level层级字段名称
@levelCodeColunmNmme nvarchar(100),--levelCode层级编码字段名称
@pidColunmName nvarchar(100),--父级ID字段名称
@tbName nvarchar(100)--表名
)
as 
begin
/*
功能：更该级所在的层级和层级编码
参数：
返回结果集：无
返回值:无
编写：ljr 2017-10-26 
测试：
*/
   set nocount on;
   declare @sql nvarchar(max)
   set @sql=''   
   --获取当前数据所在父级ID
   set @sql+='   
   declare @PID varchar(36), 
           @PLevelCode varchar(200), 
           @PLevel int 
   select @PID = isnull( '+@pidColunmName+', '''' ) 
     from '+@tbName+' (nolock) 
    where '+@idColunmName+' = '''+@idValue+''''
   --得到父级层级和层级编码   
   set @sql+='
   select @PLevelCode =isnull( '+@levelCodeColunmNmme+',''''), 
          @PLevel =isnull( [Level],0)
          from '+@tbName+'
    where '+@idColunmName+'='''+@idValue+'''
   '
   --把需要更新的当前层级的数据集存入临时表#A中  
   set @sql+='
   select C.'+@idColunmName+',
          C.'+@pidColunmName+', 
          @PLevel + 1 as '+@levelColumnName+',
          C.'+@levelCodeColunmNmme+'
     into #A 
     from '+@tbName+' C (nolock) 
    where C.'+@pidColunmName+' = @PID 
    '
   --给临时表创建 聚集索引，提高效率  
   set @sql+='create clustered index IX_#A1 on #A( '+@idColunmName+' )'
   --递归给每行以及其下级数据赋值层级编码,并把更改后的结果集存入临时表#B  
   set @sql+='
   ;with Tree( '+@idColunmName+', '+@levelColumnName+', '+@levelCodeColunmNmme+' )
     as (  select C.'+@idColunmName+',
                  C.'+@levelColumnName+', 
                  cast( @PLevelCode + right( cast( 10000 + row_number() over ( order by C.'+@idColunmName+' ) as varchar(200) ), 4 ) as varchar(200) ) as '+@levelCodeColunmNmme+'

--cast( @PLevelCode +''/''+ C.'+@idColunmName+' AS nvarchar(max) ) as '+@levelCodeColunmNmme+'
             from #A C 
            union all 
           select C.'+@idColunmName+', 
                  T.'+@levelColumnName+' + 1 as '+@levelColumnName+', 
                  cast( T.'+@levelCodeColunmNmme+' + right( cast( 10000 + row_number() over ( order by C.'+@idColunmName+' ) as varchar(200) ), 4 ) as varchar(200) ) as '+@levelCodeColunmNmme+' 

--这里是根据ID组合，父级id/父级ID/父级ID格式

--cast( T.'+@levelCodeColunmNmme+'+''/''+ C.'+@idColunmName+' AS nvarchar(max) ) as '+@levelCodeColunmNmme+'
             from '+@tbName+' C (nolock) 
                  inner join Tree T on C.'+@pidColunmName+' = T.'+@idColunmName+' )
    select distinct * into #B from Tree 
   ' 
   --给最终结果集临时表#B创建聚集索引，提高效率  
   set @sql+='   
   create clustered index IX_#B on #B( '+@idColunmName+' ) 
   '
   --更新表中的层级和层级编码,并删除之前的2张临时表 
   set @sql+='
   update C 
      set C.'+@levelColumnName+' = T.'+@levelColumnName+', 
          C.'+@levelCodeColunmNmme+' = T.'+@levelCodeColunmNmme+' 
     from '+@tbName+' C 
          inner join #B T on T.'+@idColunmName+' = C.'+@idColunmName+' 
   
   drop table #A 
   drop table #B 
   '
   print @sql    
   exec (@sql)  
   set nocount off;
end
GO