无限级菜单操作（存储过程、游标、递归、事务）修改版

/**递归删除菜单**/
if exists (select * from sysobjects where name='proc_menu_P_delete')
drop proc proc_menu_P_delete
go
create proc proc_menu_P_delete(@id int)
as 
declare @count int,@tmpid int,@err int
select @count=count(1) from tb_menu where pId=@id--通过父Id获得子集数据
if(@count>0)--判断是否存在子集数据
begin 
    --根据父Id获得子集并将子集数据Id保存到游标中
    declare ar_cursor cursor local for select id from tb_menu where pid=@id
    open ar_cursor--打开游标
    fetch next from ar_cursor into @tmpid  --取值
    /**@@FETCH_STATUS是MSSQL的一个全局变量(0:FETCH 语句成功,-1:FETCH 语句失败或此行不在结果集中,-2:被提取的行不存在
    @@fetch_status值的改变是通过fetch next from实现的,“FETCH NEXT FROM Cursor”)**/
    while @@FETCH_STATUS=0
    begin 
        exec proc_menu_P_delete @tmpid--将游标中的子集Id作为参数调用存储过程（递归）
        --递归出来以后将游标中的下一个Id赋值给变量
        fetch next from ar_cursor into @tmpid
    end
        close ar_cursor
        deallocate ar_cursor
end 
delete tb_menu where Id=@id
go

/**删除菜单**/
if exists (select * from sysobjects where name='proc_menu_delete')
drop proc proc_menu_delete
go
create proc proc_menu_delete(@id int)
as
begin
    declare @cNum int,@pId int
    begin try
        begin tran
        select @cNum=-(CNum+1),@pId=pId from tb_Menu where Id=@id
        exec proc_menu_updatecNum @pId,@cNum--修改父节点的子节点个数
        exec proc_menu_P_delete @id--删除自身及其子节点
        commit tran
    end try
    begin catch
        rollback tran
    end catch
end
go

/**修改父节点的子节点数量**/
if exists (select * from sysobjects where name='proc_menu_updatecNum')
drop proc proc_menu_updatecNum
go
create proc proc_menu_updatecNum(@pId int,@cNum int)
as
declare @tmpId int,@_pId int
    select @_pId=pId from tb_menu where id=@pId
if(@_pId<>0)
begin
    declare cor_CNum cursor local for select pId from tb_menu where id=@pId
    open cor_CNum
    fetch next from cor_CNum into @tmpId
    while @@FETCH_STATUS=0
    begin
        exec proc_menu_updatecNum @tmpId,@cNum
        fetch next from cor_CNum into @tmpId
    end
    close cor_CNum
    deallocate cor_CNum
end    
update tb_Menu set cNum=cNum+@cNum where id=@pId
go


/**修改子节点的深度**/
if exists (select * from sysobjects where name='proc_menu_updateLevel')
drop proc proc_menu_updateLevel
go
create proc proc_menu_updateLevel(@id int,@level int)
as
begin
    declare @tmpid int,@count int
    select @count=COUNT(1) from tb_Menu where pId=@id
    if(@count<>0)
        begin
            declare cur_level cursor local for select id from tb_menu where pId=@id
            open cur_level
            fetch next from cur_level into @tmpid
            while(@@FETCH_STATUS=0)
                begin
                    exec proc_menu_updateLevel @tmpid,@level
                    fetch next from cur_level into @tmpid
                end
            close cur_level
            deallocate cur_level
        end
    update tb_Menu set level=level+@level where Id=@id
end
go

/**修改节点顺序：针对Jquery的zTree控件的拖动节点排序功能**/
if exists (select * from sysobjects where name='proc_menu_updateSort')
drop proc proc_menu_updateSort
go
create proc proc_menu_updateSort(@id int,@newpId int,@sibId int,@dir int)
as
 begin
    /**
    @id：被移动的节点，@newpId：新父节点，@sibId：新父节点下的兄弟节点，@dir：方向，移动目标前面：1，移到后面：-1
    假设：原节点父节点为1，兄弟节点值以及顺序为：5,4,3,2,1，目标父节点为2，兄弟节点值以及顺序为：3,2,1
          a：现将原节点4，移动到父节点为2的子节点2的“前”面  改变后的值为：
            原：5,4,3,2,1  =>(移走3并且将大于3的减1)=> 4,3,2,1
            新：3,2,1 =>(将大于2的节点加1)=>4,2,1 =>(新插入的排序为：目标节点排序值+1)=>4,3(插入值),2,1
          b：现将原节点4，移动到父节点为2的子节点2的“后”面  改变后的值为：
            原：5,4,3,2,1  =>(移走3并且将大于3的减1)=> 4,3,2,1
            新：3,2,1 =>(将大于等于2的节点加1)=>4,3,1 =>(新插入的排序为：目标节点排序值+1)=>4,3,2(插入值),1
    **/
    begin try
      begin tran
            --修改原兄弟节点的顺序,大于被移走的节点全部-1
            declare @oldsort int,@oldpId int,@cNum int,@level int
            select @oldsort=sort,@oldpId=pId,@cNum=CNum,@level=level from tb_Menu where Id=@id
            update tb_Menu set sort=sort-1 where sort>@oldsort and pId=@oldpId
            if(@sibId<>0)--是否指定了兄弟节点
                begin
                --修改新兄弟节点的顺序、修改移动后自己节点顺序
                declare @newsort int,@sibIdsort int
                --获得目标节点的排序，并将目标节点的父节点重新付给传进来的@newpId(防止参数@newpId传错)
                select @sibIdsort=sort,@newpId=pId from tb_Menu where Id=@sibId
                if(@dir=1)--移动到目标节点的前面
                begin
                    update tb_Menu set sort=sort+1 where pId=@newpId and sort>@sibIdsort
                    update tb_Menu set sort=@sibIdsort+1 where id=@id
                end
                else if(@dir=-1)
                begin
                    update tb_Menu set sort=sort+1 where pId=@newpId and sort>=@sibIdsort
                    update tb_Menu set sort=@sibIdsort where Id=@id
                end
            end
            else if(@newpId<>0 and @sibId=0)--直接移动某个父节点最下面，即没有选中目标兄弟节点
                begin
                update tb_Menu set sort=sort+1 where pId=@newpId
                update tb_Menu set sort=1 where Id=@id
            end
            
            if(@newpId<>@oldpId)--判断是否改变了父节点
                begin
                declare @newpLevel int
                set @cNum=@cNum+1
                if(@newpId<>0)
                begin
                    select @newpLevel=level from tb_Menu where id=@newpId
                end
                else
                begin
                    set @newpLevel=0
                end
                update tb_Menu set pId=@newpId where Id=@id
                declare @levelCount int
                PRINT @newpLevel
                set @levelCount=@newpLevel+1-@level
                exec proc_menu_updateLevel @id,@levelCount
                exec proc_menu_updatecNum @newpId,@cNum
                set @cNum=-(@cNum)
                exec proc_menu_updatecNum @oldpId,@cNum
            end
        commit tran
    end try
    
    begin catch
        rollback tran
    end catch
 end
go

/**新增节点**/
if exists (select * from sysobjects where name='proc_menu_add')
drop proc proc_menu_add
go
create proc proc_menu_add(@name varchar(50),@pId int,@id int output)
as
begin
    begin try
        begin tran
            declare @pLevel int
            if(@pId<>0)
            begin
                select @pLevel=level+1 from tb_Menu where Id=@pId
            end
            else
            begin
                set @pLevel=1
            end
            insert into tb_Menu(name,twoname,pId,level,cNum,state,sort,dt,url,des)
            values(@name,'',@pId,@pLevel,0,1,0,getdate(),'','')
            set @id=@@IDENTITY
            select @id
            update tb_Menu set sort=sort+1 where pId=@pId--兄弟元素排序加1
            exec proc_menu_updatecNum @pId,1--修改父节点的子节点数量
        commit tran
    end try
    begin catch
        set @id=0
        rollback tran
    end catch
end
go

/**根据节点获得所有子节点数据**/
--方法1：连接查询
if exists (select * from sysobjects where name='proc_menu_select')
drop procedure proc_menu_select
go
create proc proc_menu_select(@id int)
as
    declare @i int 
    declare @count int, @tmpid int
    create table #tb(
                id int,
                name varchar(50),
                twoname varchar(50),
                pId int,
                level int,
                state int,
                sort int,
                url varchar(100)
                )
    select @count=COUNT(1) from tb_Menu where pId=@id
    select @i=level-1 from tb_Menu where pId=@id
    insert #tb select m.id,m.name,m.twoname,m.pId,m.level,m.state,m.sort,m.url from tb_Menu m where pId=@id
            while(@count<>0)
                begin
                set @i=@i+1
                  select @count=COUNT(1) from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i
                  insert #tb select m.id,m.name,m.twoname,m.pId,m.level,m.state,m.sort,m.url from tb_Menu m
                   left join #tb on #tb.id=m.pId where #tb.level=@i
                end    
            select id,name,twoname,pId,level,state,sort,url from #tb order by sort desc
go

--方法2：递归查询
if exists(select * from sysobjects where name='proc_menu_sele')
drop proc proc_menu_sele
go
create proc proc_menu_sele(@id int,@t int)
as
    if(@t<>0)
        begin
        declare @tmppId int
        select @tmppId=pId from tb_Menu where Id=@id
        create table #tab(
                id int,
                name varchar(50),
                twoname varchar(50),
                pId int,
                level int,
                state int,
                sort int,
                url varchar(100)
                )
        end
    declare @tmpid int,@count int
    select @count=COUNT(1) from tb_Menu where Id=@id
    if(@count<>0)
        begin
        insert INTO  #tab select id,name,twoname,pId,level,state,sort,url from tb_Menu where Id=@id
        declare cur_select cursor local for select id from tb_menu where pId=@id
        open cur_select
        fetch next from cur_select into @tmpid
        while(@@FETCH_STATUS=0)
            begin
            exec proc_menu_sele @tmpid,0
            fetch next from cur_select into @tmpid
            end
        close cur_select
        deallocate cur_select
        end
    else
        begin
        insert INTO  #tab select id,name,twoname,pId,level,state,sort,url from tb_Menu where Id=@id
        end
    declare @p int
    select @p=pId from tb_Menu where Id=@id
        if(@p=@tmppId)
        begin
            select * from #tab order by sort desc
        end
go