无限极菜单1

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_Menu')
            and   type = 'U')
   drop table tb_Menu
go

/*==============================================================*/
/* Table: tb_Menu                                               */
/*==============================================================*/
create table tb_Menu (
   Id                   int                  identity,
   name                 varchar(50)          null,
   twoname              varchar(50)          null,
   pId                  int                  null,
   level                int                  null,
   state                int                  null,
   sort                 int                  null,
   dt                   datetime             null,
   url                  varchar(100)         null,
   des                  varchar(1)           null,
   cNum                 int                  null,
   constraint PK_TB_MENU primary key (Id)
)
go

 

------------------------------------递归删除菜单----------------------------------------
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  --取值
    while @@FETCH_STATUS=0--是MSSQL的一个全局变量(0 FETCH 语句成功,-1 FETCH 语句失败或此行不在结果集中,-2 被提取的行不存在,@@fetch_status值的改变是通过fetch next from实现的,“FETCH NEXT FROM Cursor”)
    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


------------------------------------end-------------------------------------------

------------------------------------修改父节点的子节点数量-----------------------------
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

-------------------------------------end---------------------------------------------

------------------------------------修改子节点的深度----------------------------------

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
                    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
    假设：原节点顺序为：5,4,3,2,1，@sibId及目标节点为3
    情形1：将节点插到3的前面，那么需要修改5、4的值，加1 改变后的节点为：6、5、（插入节点=4）、3、2、1
    情形2：将节点插到3的后面，那么需要修改5、4、3的值 加1 改变后的节点为：6、5、4、（插入节点=3）、2、1
    **/
    --修改原兄弟节点的顺序
    declare @oldsort int
    select @oldsort=sort from tb_Menu where Id=@id
    update tb_Menu set sort=sort-1 where sort>@oldsort
    if(@sibId<>0)
    begin
        --修改新兄弟节点的顺序、修改移动后自己节点顺序
        declare @newsort int,@sibIdsort int
        select @sibIdsort=sort 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
    if(@newpId<>0)
    begin
        declare @oldpId int,@cNum int
        select @oldpId=pId,@cNum=-(CNum+1) from tb_Menu where Id=@id
        update tb_Menu set pId=@newpId where Id=@id
        exec proc_menu_updatecNum @oldpId,@cNum

        set @cNum=-(@cNum)
        exec proc_menu_updatecNum @newpId,@cNum
        update tb_Menu set sort=sort+1 where pId=@newpId
        update tb_Menu set sort=0 where Id=@id
    end
 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




select * from tb_menu;
--exec proc_menu_delete 2@id int,@newpId int,@sibId int,@dir int
exec proc_menu_updateSort 1,2,4,-1
select * from tb_menu;