存储过程实现无限级分类（2）

存储过程实现无限级分类（2）2011-03-19 博客园兴百放删除的存储过程

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
  Begin
      Rollback Tran
      Return 1 --有子节点
End
      Else
Begin
      Rollback Tran
      Return 2--未知错误
End
    End
GO