无限级分类的非递归实现(存储过程版）

End

Insert Into Tb_Column(Column_ID,Column_Name,Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Intro)

Values(@Column_ID,@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column_Order,@Column_Intro)

IF @@Error<>0
Begin
Set @Err=1
Goto theEnd
End

--更新当前记录之后的记录的ORDER
--Update Tb_Column Set Column_Order = Column_Order+1 Where Column_Order  > @Column_Order

theEnd:
IF @Err=0
Begin
Commit Tran
Return @Column_ID
End
Else
Begin
    Rollback Tran
Return 0
End
GO

=============================================================================
存储过程二：删除分类
CREATE PROCEDURE sp_Column_Delete
(
@Column_ID int
)
AS
Declare @Err As int
Set @Err = 0
Begin Tran
--首先查询该节点下是否有子节点
Select Column_ID From Tb_Column Where Parent_ID = @Column_ID
IF @@RowCount<>0
    Begin
    Set @Err = 1
    Goto theEnd
    End

--获取该节点的Column_Order,为了删除后整理其他记录的顺序
Declare @Column_Order As int
Select @Column_Order = Column_Order From Tb_Column Where Column_ID = @Column_ID
IF @Column_Order Is NUll
    Begin
      Set @Err =2
      Goto theEnd
    End

--更新其他记录的Column_Order
Update Tb_Column Set Column_Order = Column_Order -1 Where Column_Order >@Column_Order
IF @@Error<>0
    Begin
      Set @Err =3
      Goto theEnd
    End

--删除操作
Delete From Tb_Column Where Column_ID=@Column_ID
IF @@Error<>0
    Begin
      Set @Err =4
      Goto theEnd
  End

--更新其他记录的Column_ID
--Update Tb_Column Set Column_ID= Column_ID - 1 Where Column_ID >@Column_ID
--IF @@Error<>0
--    Begin
--      Set @Err =5
--      Goto theEnd
--    End

theEnd:
IF @Err = 0
    Begin
      Commit Tran
      Return 0 --删除成功
    End
Else
    Begin
      IF @Err=1

上一页 [1] [2] [3] [4] [5] [6] 下一页

复制本页网址和标题，发送给你QQ/Msn的好友一起分享

上一篇:在ASP.NET Atlas中调用Web Service—处理错误 超时以及响应用户的取消操作

下一篇:借助工具 为BitComet轻松去广告