CREATEPROCEDURE sp_Column_Insert ( @Parent_IDint, @Column_Namenvarchar(50), @Column_Intronvarchar(1000) ) AS Declare@ErrAsint Set@Err=0 BeginTran --通过现有记录获取栏目ID Declare@Column_IDAsint Declare@Column_DepthAsint Select@Column_ID=Max(Column_ID) From Tb_Column IF@Column_IDIsNotNull Set@Column_ID=@Column_ID+1 Else Set@Column_ID=1 --判断是否是顶级栏目,设置其Column_Path和Column_Order Declare@Column_PathAsnvarchar(1000) Declare@Column_OrderAsint IF@Parent_ID=0 Begin Set@Column_Path=Ltrim(Str(@Column_ID)) Select@Column_Order=Max(Column_Order) From Tb_Column IF@Column_OrderIsNotNull Set@Column_Order=@Column_Order+1 Else--如果没有查询到记录,说明这是第一条记录 Set@Column_Order=1 --深度 Set@Column_Depth=1 End Else Begin --获取父节点的路径和深度 Select@Column_Path= Column_Path ,@Column_Depth= Column_Depth From Tb_Column Where Column_ID=@Parent_ID IF@Column_PathIsNull Begin Set@Err=1 Goto theEnd End --获取同父节点下的最大序号 Select@Column_Order=Max(Column_Order) From Tb_Column Where Column_Path like ''+@Column_Path+'|%'or Column_ID =@Parent_ID IF@Column_OrderIsNotNull--如果序号存在,那么将该序号后的所有序号都加1 Begin --更新当前要插入节点后所有节点的序号 Update Tb_Column Set Column_Order = Column_Order +1Where Column_Order >@Column_Order --同父节点下的最大序号加上1,构成自己的序号 Set@Column_Order=@Column_Order+1 End Else Begin Set@Err=1 Goto theEnd End --父节点的路径加上自己的ID号,构成自己的路径 Set@Column_Path=@Column_Path+'|'+Ltrim(Str(@Column_ID)) --深度 Set@Column_Depth=@Column_Depth+1 End InsertInto 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 CommitTran Return@Column_ID End Else Begin RollbackTran Return0 End GO
删除分类(只能删除最底层分类)
CreatePROCEDURE sp_Column_Delete ( @Column_IDint ) AS Declare@ErrAsint Set@Err=0 BeginTran --首先查询该节点下是否有子节点 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_OrderAsint Select@Column_Order= Column_Order From Tb_Column Where Column_ID =@Column_ID IF@Column_OrderIsNUll Begin Set@Err=2 Goto theEnd End --更新其他记录的Column_Order Update Tb_Column Set Column_Order = Column_Order -1Where Column_Order >@Column_Order IF@@Error<>0 Begin Set@Err=3 Goto theEnd End --删除操作 DeleteFrom 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 CommitTran Return0--删除成功 End Else Begin IF@Err=1 Begin RollbackTran Return1--有子节点 End Else Begin RollbackTran Return2--未知错误 End End GO
更新分类
CreatePROCEDURE sp_Column_Update ( @Column_IDint, @Parent_IDint, @Column_Namenvarchar(50), @Column_Intronvarchar(1000) ) AS Declare@ErrAsint Set@Err=0 BeginTran --获取修改前的:Parent_ID,Column_Depth,Column_Order Declare@oParent_IDAsint Declare@oColumn_DepthAsint Declare@oColumn_OrderAsint Declare@oColumn_PathAsnvarchar(1000) Select@oParent_ID= Parent_ID, @oColumn_Depth= Column_Depth,@oColumn_Order= Column_Order, @oColumn_Path= Column_Path From Tb_Column Where Column_ID =@Column_ID IF@oParent_IDIsNull Begin Set@Err=1 Goto theEnd End --如果父ID没有改变,则直接修改栏目名和栏目简介 IF@oParent_ID=@Parent_ID Begin Update Tb_Column Set Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Set@Err=2 Goto theEnd End Declare@nColumn_PathAsnvarchar(1000) Declare@nColumn_DepthAsint Declare@nColumn_OrderAsint --获取当前节点作为父节点所包含的节点数[包括自身] 注:如果返回 “1” 说明是单节点 Declare@theCountAsint Select@theCount=Count(Column_ID) From Tb_Column Where Column_ID=@Column_IDor Column_Path like''+@oColumn_Path+'|%' IF@theCountIsNull Begin Set@Err=3 Goto theEnd End IF@Parent_ID=0--如果是设置为顶级节点,将节点设置为最后一个顶级节点 Begin --Print '设置为顶级栏目' Set@nColumn_Path=Ltrim(Str(@Column_ID)) Set@nColumn_Depth=1 Select@nColumn_Order=Max(Column_Order) From Tb_Column IF@nColumn_OrderIsNULL Begin Set@Err=4 Goto theEnd End Set@nColumn_Order=@nColumn_Order-@theCount+1 --更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序 --Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的:Column_Order' Update Tb_Column Set Column_Order = Column_Order-@theCountWhere (Column_Order >@oColumn_Order) And (Column_Path Notlike''+@oColumn_Path+'|%') IF@@Error<>0 Begin Set@Err=7 Goto theEnd End --Print '更新本栏目的:Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro' Print'Order : '+Ltrim(Str(@nColumn_Order)) Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth,Column_Order =@nColumn_Order, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=5 Goto theEnd End --Print '更新本栏目下的所有子栏目的:Column_Path,Column_Depth,Column_Order' Update Tb_Column Set Column_Path =Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+( @nColumn_Order-@oColumn_Order) Where Column_Path like''+@oColumn_Path+'|%' IF@@Error<>0 Begin Set@Err=6 Goto theEnd End End Else Begin --获取未来父节点的相关信息,并设置本节点的相关值 Select@nColumn_Depth= Column_Depth,@nColumn_Path= Column_Path From Tb_Column Where Column_ID =@Parent_ID IF@nColumn_DepthIsNULLor@nColumn_PathIsNull Begin Set@Err=8 Goto theEnd End Set@nColumn_Depth=@nColumn_Depth+1 Select@nColumn_Order=Max(Column_Order) From Tb_Column Where Column_ID =@Parent_IDor Column_Path like''+@nColumn_Path+'|%' IF@nColumn_OrderIsNULL Begin Set@Err=9 Goto theEnd End Set@nColumn_Path=@nColumn_Path+'|'+Ltrim(Str(@Column_ID)) IF@nColumn_Order=@oColumn_Order+1--如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变 Begin Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=10 Goto theEnd End End Set@nColumn_Order=@nColumn_Order+1 --更新三部分 1 本树更改之前的后面(或前面)记录的顺序 1 节点本身 3 所有子节点 --分为向上移或象下移 --Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的:Column_Order' IF@nColumn_Order<@oColumn_Order Begin Update Tb_Column Set Column_Order = Column_Order+@theCountWhere Column_Order<@oColumn_OrderAnd Column_Order >=@nColumn_OrderAnd (Column_Path Notlike''+@oColumn_Path+'|%') And Column_ID<>@Column_ID IF@@Error<>0 Begin Set@Err=12 Goto theEnd End End Else Begin Update Tb_Column Set Column_Order = Column_Order-@theCountWhere Column_Order >@oColumn_OrderAnd Column_Order<@nColumn_OrderAnd (Column_Path Notlike''+@oColumn_Path+'|%') And Column_ID<>@Column_ID IF@@Error<>0 Begin Set@Err=13 Goto theEnd End End --Print '更新本栏目的:Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro' Print'Order : '+Ltrim(Str(@nColumn_Order)) IF@nColumn_Order>@oColumn_Order Set@nColumn_Order=@nColumn_Order-@theCount Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth,Column_Order =@nColumn_Order, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=10 Goto theEnd End --Print '更新本栏目下的所有子栏目的:Column_Paht,Column_Depth,Column_Order' Update Tb_Column Set Column_Path =Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+(@nColumn_Order-@oColumn_Order) Where Column_Path like''+@oColumn_Path+'|%' IF@@Error<>0 Begin Set@Err=11 Goto theEnd End End theEnd: IF@Err<>0--如果有错误则返回错误号 Begin RollbackTran Return@Err End Else--如果没有错误就返回0 Begin CommitTran Return0 End GO
显示分类
CreatePROCEDURE sp_Column_List AS Select Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Intro FROM Tb_Column orDERBY Column_Order GO
创建表if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[Tb_Column]) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table [dbo].[Tb_Column]GOCreate TABLE [dbo].[Tb_Column] (