SQL无限分类存储过程整理2

模仿参考:动易无限分类

优点:算得上是真正的无限分类，不过ParentPath是有局限性,ClassID是标识列自动增一.

缺点:新增过程中ParentID不存在还没有完善，会插入NULL数据.后面三个存储过程还在测试。

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArticleClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ArticleClass]
GO

CREATE TABLE [dbo].[ArticleClass] (
 [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
 [ClassName] [nvarchar] (100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
 [ParentID] [int] NULL ,
 [ParentPath] [nvarchar] (255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
 [Depth] [int] NULL ,
 [RootID] [int] NULL ,
 [Child] [int] NULL ,
 [PrevID] [int] NULL ,
 [NextID] [int] NULL ,
 [OrderID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ArticleClass] ADD
 CONSTRAINT [DF_ArticleClass_ParentID_65E11278] DEFAULT (0) FOR [ParentID],
 CONSTRAINT [DF_ArticleClass_Depth_66D536B1] DEFAULT (0) FOR [Depth],
 CONSTRAINT [DF_ArticleClass_RootID_67C95AEA] DEFAULT (0) FOR [RootID],
 CONSTRAINT [DF_ArticleClass_Child_68BD7F23] DEFAULT (0) FOR [Child],
 CONSTRAINT [DF_ArticleClass_PrevID_69B1A35C] DEFAULT (0) FOR [PrevID],
 CONSTRAINT [DF_ArticleClass_NextID_6AA5C795] DEFAULT (0) FOR [NextID],
 CONSTRAINT [DF_ArticleClass_OrderID_6B99EBCE] DEFAULT (0) FOR [OrderID],
 CONSTRAINT [PK_ArticleClass] PRIMARY KEY  CLUSTERED
 (
  [ClassID]
 )  ON [PRIMARY]
GO

exec sp_addextendedproperty N'MS_Description', N'主键', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassID'
GO
exec sp_addextendedproperty N'MS_Description', N'类别名称', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassName'
GO
exec sp_addextendedproperty N'MS_Description', N'父类ID', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ParentID'
GO
exec sp_addextendedproperty N'MS_Description', N'父类路径', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ParentPath'
GO
exec sp_addextendedproperty N'MS_Description', N'深度', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'Depth'
GO
exec sp_addextendedproperty N'MS_Description', N'根类别编号', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'RootID'
GO
exec sp_addextendedproperty N'MS_Description', N'子类数量', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'Child'
GO
exec sp_addextendedproperty N'MS_Description', N'前一类别编号', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'PrevID'
GO
exec sp_addextendedproperty N'MS_Description', N'后一类别编号', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'NextID'
GO
exec sp_addextendedproperty N'MS_Description', N'排列顺序', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'OrderID'

GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Add_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Add_ArticleClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Delete_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Delete_ArticleClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Move_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Move_ArticleClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Update_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Update_ArticleClass]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE sp_Add_ArticleClass   
 @ParentID int,
 @ClassName varchar(100)
as

declare @parentDepth int, @parentPath varchar(255), @parentName varchar(100)
declare @depth int
declare @maxRootID int, @RootID int
declare @orderId int,@prevOrderId int
declare @prevId int

select @maxRootID =ISNULL( max(RootID),0) From ArticleClass

-- 如果是加在根结点上
if @ParentID = 0
    begin
        select @prevId = ClassID from ArticleClass where RootID = @maxRootID and Depth = 0
        set @ParentID     = 0
        set @parentPath = '0'
        set @orderId     = 0
        set @RootID    = @maxRootID + 1
        set @prevId     = 0
        set @depth    = 0
    end
   
-- 如果不是加在根结点上   
else if @ParentID > 0
    begin
        declare @childNumber int
        select
            @RootID            = RootID,
            @parentName        = ClassName,
            @depth            = Depth + 1,
            @parentPath        = ParentPath,
            @childNumber        = Child,
            @prevOrderId        = OrderId
        from ArticleClass  where ClassID = @ParentID
       
        set @orderId = @prevOrderId + 1

        if @parentPath = '0'
            begin
 set @parentPath = cast(@ParentID as varchar)
            end
        else
            begin    
 set @parentPath = @parentPath + ',' + cast(@ParentID as varchar)
            end       
   
        if @childNumber > 0
            begin
                select @prevOrderId = max(OrderId) from ArticleClass where ParentID=@ParentID
                select @prevId = ClassID from ArticleClass where ParentID=@ParentID and OrderId=@prevOrderId
                set @orderId = @prevOrderId + 1            
            end
        else
            begin
                set @prevId = 0
            end       
    end

-- 插入一个新的类别
insert into ArticleClass
    (ClassName,
     ParentID,
     ParentPath,
     RootID,
     OrderId,
     Depth,
     Child,
     PrevId,
     NextID)
values
    (@ClassName,
     @ParentID,
     @parentPath,
     @RootID,
     @orderId,
     @depth,
     0,
     @prevId,
     0)

-- 更新受影响的行
declare @newClassID int
select @newClassID = max(ClassID) from ArticleClass
if @prevId > 0
    update ArticleClass set NextID = @newClassID where ClassID = @prevId   

if @ParentID > 0
    begin
        --父结点所拥有的子孙结点数加一
        update ArticleClass set Child = Child + 1 where ClassID = @ParentID
        --比被插入的OrderId大的通通加一
        update ArticleClass
        set OrderId = OrderId + 1
        where @RootID = RootID and OrderId >= @orderId and ClassID != @newClassID
    end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE sp_Delete_ArticleClass  
@ClassID int=0,
@ErrInfo nvarchar(100)='' output
As

if(@ClassID=0)
begin
Set @ErrInfo='请选择要删除的类别'
return
end

if not exists(Select ClassID,RootID,Depth,ParentID,Child,PrevID,NextID From ArticleClass Where ClassID=@ClassID)
begin
--print('d')
Set @ErrInfo='栏目不存在，或者已被删除'
return
end

declare @RsChild int
declare @PrevID int
declare @NextID int
declare @Depth int
declare @ParentID int

Select @Depth=Depth,@ParentID=ParentID,@RsChild=Child,@PrevID=PrevID,@NextID=NextID From ArticleClass Where ClassID=@ClassID

if @RsChild>0
begin
--print('d')
Set @ErrInfo='要删除的栏目包含有子栏目，请先删除其子栏目后再进行删除本栏目的操作'
return

end

if(@Depth>0)
begin
update ArticleClass set child=child-1 where ClassID=@ParentID
end
delete From ArticleClass Where ClassID=@ClassID
if @PrevID>0
begin
   update ArticleClass set NextID=@NextID where ClassID=@PrevID
end
if @NextID>0
begin
   update ArticleClass set PrevID=@PrevID where ClassID=@NextID
end
Set @ErrInfo='成功'
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE sp_Move_ArticleClass
@ParentID int=0,
@ClassID int=0,
@ErrInfo nvarchar(200)='' output
As

declare @oldParent int--原先分类的父类ID
declare @oldClassID int--原先分类的ID
declare @oldRootID int --原先根栏目ID
declare @oldParentPath nvarchar(100)--原父类栏目路径
declare @Depth int
declare @Child int
declare @PrevID int
declare @NextID int
declare @PrevOrderID int
declare @NewParentID int
declare @iParentID int
declare @iParentPath nvarchar(200)

declare @mParentPath nvarchar(100)

declare @ClassCount int

if not exists(Select * from ArticleClass Where ClassID=@ClassID)
begin
   Set @ErrInfo='找不到指定的栏目'
   return 1
end
else
begin
   Select @oldParent=ParentID,@oldClassID=ClassID,@oldRootID=RootID,@oldParentPath=ParentPath,@Depth=Depth,@Child=Child,@PrevID=PrevID,@NextID=NextID
   from ArticleClass
   Where ClassID=@ClassID
end

 

if @oldParent<>@ParentID --更改了所在父路径要做一系列检查
begin
   if @ParentID=@oldClassID
   begin
    Set @ErrInfo='目标栏目位置不能是自己！'
    return 1  
   end

   --判断所指定的栏目是否是外部栏目或者本栏目下的栏目
   if @oldParent=0
   begin
    if @ParentID>0
    begin--@ParentID=0
     if not exists(Select RootID From ArticleClass where ClassID=@ParentID)
     begin
      Set @ErrInfo='顶部栏目不能移动'
      return 1  
     end
     else
     begin
      declare @currRootID int  
      Select @currRootID=RootID From ArticleClass where ClassID=@ParentID
      if @oldRootID=@currRootID
      begin
      Set @ErrInfo='不能指定栏目下的栏目作为移动目标位置'
      return 1      
      end
 
     end
    end
 
   end
   else
   begin
    if exists(select ClassID From ArticleClass where ParentPath like ''+@oldParentPath+','+Convert(nvarchar(100),@oldClassID)+'%' and ClassID=@ParentID)
    begin
      Set @ErrInfo='不能指定??目的下??目作?所??目'
      return 1   
    end 
 
   end
end

if @oldParent=0
begin
   set @NewParentID=@oldClassID
   set @iParentID=0
end
else
begin
   set @NewParentID=@oldParent
   set @iParentID=@oldParent
end
   --假如更改了所??目
   --需要更新其原?所??目信息，包括深度、父?ID、?目?、排序、?承版主等?据
   --需要更新?前所??目信息
declare @maxRootID int

select @maxRootID=isnull(max(RootID),0) From ArticleClass

if @NewParentID<>@ParentID and not(@iParentID=0 and @ParentID=0)
begin

   if @PrevID>0
   begin
    update ArticleClass Set NextID=@NextID Where ClassID=@PrevID
   end
   if @NextID>0
   begin
    update ArticleClass Set PrevID=@PrevID Where ClassID=@NextID
   end
 
   print('@iParentID的值是:'+Convert(nvarchar(100),@iParentID))
   print('@ParentID的值是:'+Convert(nvarchar(100),@ParentID))
   --return
 
   if @iParentID>0 and @ParentID=0   --如果原?不是一?分?改成一?分?
   begin
  
    Select @PrevID=ClassID From ArticleClass where RootID=@maxRootID and Depth=0
    update ArticleClass set NextID=@ClassID where RootID=@maxRootID and Depth=0
    set @maxRootID=@maxRootID+1

  

    --更新?前?目?据
    update ArticleClass set depth=0,OrderID=0,RootID=@maxRootID,ParentID=0,ParentPath='0',PrevID=@PrevID,NextID=0
    where ClassID=@ClassID
    --如果有下??目，?更新其下??目?据。下??目的排序不需考?，只需更新下??目深度和一?排序ID(RootID)?据
    if @Child>0
    begin
     declare @i int
     set @i=0
     set @oldParentPath=@oldParentPath+','

     --print('@oldParentPath的值是:'+Convert(nvarchar(100),@oldParentPath))
     --print('Select ParentPath,ClassID From ArticleClass   where ParentPath like ''%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%''')
   
     --return
   
     declare @CurParentPath nvarchar(100)
     declare @CurClassID int

     Declare   Cur CURSOR FOR
      Select ParentPath,ClassID From ArticleClass   where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%'
     Open   Cur
     Fetch   Cur   Into @CurParentPath,@CurClassID
     While   @@FETCH_STATUS=0  
     begin
      set @i=@i+1
      set @mParentPath=replace(@CurParentPath,@oldParentPath,'')
    
      update ArticleClass Set depth=depth-@depth,RootID=@maxRootID,ParentPath=@mParentPath where ClassID=@CurClassID
      Fetch   Cur   Into @CurParentPath,@CurClassID
     end
     Close   Cur  
     deallocate Cur
    end
    update ArticleClass set child=child-1 where ClassID=@iParentID

   end
   else if @iParentID>0 and @ParentID>0 --如果是?一?分?目移?到其他分?目下
   begin
    --得到?前?目的下?子?目?
  
    set @oldParentPath=@oldParentPath+','

    Select @ClassCount=isnull(Count(ClassID),1) From ArticleClass Where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%'
    --?得目??目的相?信息

    declare @Childtmp int
    declare @ClassIDtmp int
    declare @OrderIDtmp int
    declare @ParentPathtmp nvarchar(200)
    declare @RootIDtmp int
    declare @depthtmp int

    Select @Childtmp=Child,@ClassIDtmp=ClassID,@OrderIDtmp=OrderID,@ParentPathtmp=ParentPath,@RootIDtmp=RootID,@depthtmp=depth
    from ArticleClass where ClassID=@ParentID

    if @Childtmp>0
    begin
     --得到与本?目同?的最后一??目的OrderID
     Select @PrevOrderID=Max(OrderID) From ArticleClass Where ParentID=@ClassIDtmp
     --得到与本?目同?的最后一??目的ClassID
     Select @PrevID=ClassID From ArticleClass Where ParentID=@ClassIDtmp and OrderID=@PrevOrderID
     Update ArticleClass Set NextID=@ClassID Where ParentID=@ClassIDtmp and OrderID=@PrevOrderID
     --得到同一父?目但比本?目??大的子?目的最大OrderID，如果比前一?值大，?改用??值。
     declare @MaxOrderIDtmp int
     if exists( Select Max(OrderID) from ArticleClass Where ParentPath like ''+@ParentPathtmp+','+Convert(nvarchar(100),@ClassIDtmp)+',%')
     begin
      Select @MaxOrderIDtmp= Max(OrderID) from ArticleClass Where ParentPath like ''+@ParentPathtmp+','+Convert(nvarchar(100),@ClassIDtmp)+',%'
      if @MaxOrderIDtmp is not null
      begin
       if @MaxOrderIDtmp>@PrevOrderID
       begin
        set @PrevOrderID=@MaxOrderIDtmp
       end
      end
     end
    end
    else
    begin
     set @PrevID=0
     set @PrevOrderID=@OrderIDtmp
    end
    /*
    print('@PrevID:'+Convert(nvarchar(200),@PrevID))
    print('@PrevOrderID:'+Convert(nvarchar(200),@PrevOrderID))
    print('@OrderIDtmp:'+Convert(nvarchar(200),@OrderIDtmp))
    print('@ClassCount:'+Convert(nvarchar(200),@ClassCount))

    print(@ParentPathtmp+','+Convert(nvarchar(100),@ClassIDtmp))
    print('Update ArticleClass Set OrderID=OrderID+1 where RootID='+Convert(nvarchar(200),@RootIDtmp)+' and OrderID>'+Convert(nvarchar(200),@PrevOrderID))
    return
    */

    --在?得移???的?目?后更新排序在指定?目之后的?目排序?据
    Update ArticleClass Set OrderID=OrderID+@ClassCount+1 where RootID=@RootIDtmp and OrderID>@PrevOrderID
    --更新?前?目?据
    Update ArticleClass Set depth=@depthtmp+1,OrderID=@PrevOrderID+1, RootID=@RootIDtmp,ParentID=@ParentID,ParentPath=@ParentPathtmp+','+Convert(nvarchar(100),@ClassIDtmp),PrevID=@PrevID,NextID=0 Where ClassID=@ClassID
    --如果有子?目?更新子?目?据，深度?原?的相?深度加上?前所??目的深度
    declare @i1 int
     set @i1=0
   declare @CurParentPath1 nvarchar(200)
    declare @CurClassID1 int
    Declare   Cur1 Cursor SCROLL For
     Select ParentPath,ClassID From ArticleClass   where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%' order by OrderID
    Open   Cur1
    Fetch   Cur1   Into @CurParentPath1,@CurClassID1
    While   @@FETCH_STATUS=0  
    begin
     set @i1=@i1+1
     set @iParentPath=@ParentPathtmp+','+Convert(nvarchar(100),@ClassIDtmp)+','+replace(@CurParentPath1,@oldParentPath,'')
     --print('@iParentPath的值?'+@iParentPath)
     update ArticleClass
     Set depth=depth-@depth+@depthtmp+1,OrderId=@PrevOrderID+@i1,RootID=@RootIDtmp,ParentPath=@iParentPath
     where ClassID=@CurClassID1
     Fetch   Cur1   Into @CurParentPath1,@CurClassID1
    end
    Close   Cur1  
    Deallocate   cur1
    --更新所指向的上??目的子?目?
    update ArticleClass set child=child+1 where ClassID=@ParentID
    update ArticleClass set child=child-1 where ClassID=@iParentID
   end
   else --如果原?是一??目改成其他?目的下??目
   begin
    --得到移?的?目??
    Select @ClassCount=count(ClassID) From ArticleClass Where RootID=@oldRootID

    --?得目??目的相?信息
    declare @Childtmp2 int
    declare @ClassIDtmp2 int
    declare @ParentPathtmp2 nvarchar(200)
    declare @OrderIDtmp2 int
    declare @RootIDtmp2 int
    declare @ParentIDtmp2 int
    declare @depthtmp2 int

    select @Childtmp2=Child,@ClassIDtmp2=ClassID,@ParentPathtmp2=ParentPath,@RootIDtmp2=RootID,@ParentIDtmp2=ParentID,@depthtmp2=depth,@OrderIDtmp2=OrderID
    From ArticleClass where ClassID=@ParentID

 

    if @Childtmp2>0
    begin
     --得到与本?目同?的最后一??目的OrderID
     select @PrevOrderID=isnull(Max(OrderID),0) From ArticleClass where ParentID=@ClassIDtmp2
     Select @PrevID=isnull(ClassID,0) From ArticleClass Where ParentID=@ClassIDtmp2 and OrderID=@PrevOrderID
     update ArticleClass Set NextID=@ClassID where ParentID=@ClassIDtmp2 and OrderID=@PrevOrderID
     --得到同一父?目但比本?目??大的子?目的最大OrderID，如果比前一?值大，?改用??值。
     declare @MaxOrderIDtmp2 int
     if exists( Select Max(OrderID) from ArticleClass Where ParentPath like ''+@ParentPathtmp2+','+Convert(nvarchar(100),@ClassIDtmp2)+',%')
     begin
      Select @MaxOrderIDtmp2= Max(OrderID) from ArticleClass Where ParentPath like ''+@ParentPathtmp2+','+Convert(nvarchar(100),@ClassIDtmp2)+',%'
      if @MaxOrderIDtmp2 is not null
      begin
       if @MaxOrderIDtmp2>@PrevOrderID
       begin
        set @PrevOrderID=@MaxOrderIDtmp2
       end
      end
     end

    end
    else
    begin
     set @PrevID=0
     set @PrevOrderID=@OrderIDtmp2   
    end

    --在?得移???的?目?后更新排序在指定?目之后的?目排序?据
    Update ArticleClass Set OrderID=OrderID+ @ClassCount+1 where RootID=@RootIDtmp2 and OrderID>@PrevOrderID
    Update ArticleClass Set PrevID=@PrevID,NextID=0 where ClassID=@ClassID
   
    declare @i2 int
    Set @i2=0

    declare @ParentIDtmp21 int
    declare @ClassIDtmp21 int
    declare @depthtmp21 int
    declare @RootIDtmp21 int
    declare @ParentPathtmp21 nvarchar(200)

    Declare   Cur2 Cursor SCROLL For
     Select ParentID,ClassID,depth,RootID,ParentPath
     From ArticleClass   where RootID=@oldRootID order by OrderID
    Open   Cur2
    Fetch   Cur2   Into @ParentIDtmp21,@ClassIDtmp21,@depthtmp21,@RootIDtmp21,@ParentPathtmp21
    While   @@FETCH_STATUS=0  
    begin
     Set @i2=@i2+1

     if @ParentIDtmp21=0
     begin
      Set @oldParentPath=Convert(nvarchar(200),@ParentPathtmp2)+','+Convert(nvarchar(200),@ClassIDtmp2)

      Update ArticleClass Set depth=depth+@depthtmp2+1,OrderId=@PrevOrderID+@i2,RootID=@RootIDtmp2,ParentPath=@oldParentPath,ParentID=@ParentID where ClassID=@ClassIDtmp21
    
     end
     else
     begin
      Set @oldParentPath=Convert(nvarchar(200),@ParentPathtmp2)+','+Convert(nvarchar(100),@ClassIDtmp2)+','+replace(@ParentPathtmp21,'0,','')
      update ArticleClass set depth=depth+@depthtmp2+1,OrderID=@PrevOrderID+@i2,RootID=@RootIDtmp2,ParentPath=@oldParentPath where ClassID=@ClassIDtmp21
     end
     Fetch   Cur2   Into @ParentIDtmp21,@ClassIDtmp21,@depthtmp21,@RootIDtmp21,@ParentPathtmp21
    end
    Close   Cur2
    Deallocate   Cur2

    update ArticleClass set child=child+1 where ClassID=@ParentID
  
   end

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

CREATE PROCEDURE sp_Update_ArticleClass
@ClassID int=0,
@cRootID int =0,
@MoveNum int=0
As
--得到本栏目的PrevID,NextID
declare @PrevID int,@NextID int,@MaxRootID int,@LastRoot int

select @PrevID=PrevID,@NextID=NextID from ArticleClass where ClassID=@ClassID

--先修改上一栏目的NextID和下一栏目的PrevID
if @PrevID>0
begin
update ArticleClass set NextID=@NextID where ClassID=@PrevID
end

if @NextID>0
begin
update ArticleClass set PrevID=@PrevID where ClassID=@NextID
end

--declare @MaxRootID int
Select @MaxRootID=isnull(Max(RootID),0) From ArticleClass
--print(@MaxRootID )
set @MaxRootID=@MaxRootID+1 --4
--先将当前栏目和当前栏目的子栏目移动至最后根类别最后一个类别
Update ArticleClass Set RootID=@MaxRootID where RootID=@cRootID --@cRootID==2   RootID=4

--然后将位于本栏目前面栏目的RootID依次加一
declare @i int,@tRootID int ,@CurClassID int
set @i=1

Declare   @tmpCursor   CURSOR
   --SET @tmpCursor = CURSOR SCROLL For
SET @tmpCursor = CURSOR SCROLL DYNAMIC SCROLL_LOCKS For

Select RootID,ClassID From ArticleClass where ParentID=0 and RootID<@cRootID order by RootID desc
for update
Open   @tmpCursor
FETCH @tmpCursor INTO @tRootID,@CurClassID
While   @@FETCH_STATUS=0  
begin
set @LastRoot=@tRootID
-- print('@tRootID?里的值:'+Convert(nvarchar(100),@tRootID))
Update ArticleClass Set RootID=RootID+1 where RootID=@tRootID
set @i=@i+1
if @i> @MoveNum
begin
   Update ArticleClass set PrevID=@ClassID where current of @tmpCursor
   update ArticleClass set NextID=@CurClassID where ClassID=@ClassID
   --print('Update ArticleClass Set PrevID='+convert(nvarchar(100),@ClassID)+' where ParentID=0 and RootID<'+convert(nvarchar(100),@cRootID)+'')
   goto FAILURE
end

Fetch Next From   @tmpCursor   Into @tRootID,@CurClassID
--print('@tRootID?里的值:'+Convert(nvarchar(100),@tRootID))
end
FAILURE:
--print(@tRootID)

Fetch Next From   @tmpCursor   Into @tRootID,@CurClassID
--print(@LastRoot)

if @@fetch_status<>0
begin
Update ArticleClass Set PrevID=0 where ClassID=@ClassID
end

else
begin
Update ArticleClass set NextID=@ClassID where current of @tmpCursor
update ArticleClass set PrevID=@CurClassID where ClassID=@ClassID
end
CLOSE @tmpCursor
DEALLOCATE @tmpCursor

--然后再将当前栏目移动到相对应的位置，包括子栏目
update ArticleClass set RootID=@LastRoot where RootID=@MaxRootID
--print('update ArticleClass set RootID='+Convert(nvarchar(100),@tRootID)+' where RootID='+Convert(nvarchar(100),@MaxRootID))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO