-- ============================= -- Author: <杨俊明,Jimmy.yang@cntvs.com or yjmyzz@126.com> -- Description: <无限级分类_删除分类> -- Return : 0删除正常,-1父类下还有子类 -- =========================== Alter PROCEDURE [dbo].[up_Class_DeleteEx] @clsId uniqueidentifier, --要删除的分类 @withChild bit --是否级联删除子类 AS BEGIN SET NOCOUNT ON; Declare @so_MaxOrders int; Declare @s_Childs int; Declare @clsType Nvarchar(50); Declare @O_RootId int; Select @O_RootId = F_RootId,@clsType = F_Type From T_Class where F_ID=@clsId;
If (@WithChild=1) begin Select @so_maxOrders=max(F_orders) From T_class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%'; --得到自身系的最大排序号 Select @s_Childs= Count(F_ID) from t_Class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%'; Delete from T_Class where ','+F_parentIdStr+',' like '%' + convert(varchar(100),@clsId) + '%' Delete from T_Class Where F_Id=@ClsId Update T_Class Set F_Orders = F_Orders - @s_childs where F_Type=@clsType And F_RootId=@O_RootId And F_Orders > @so_maxOrders; return 0 end else --如果不级联删除,又有子节点,则返回-1 If exists(select F_id from T_Class where ',' + F_parentIdStr + ',' like '%' + convert(varchar(100),@clsId) + '%') return -1 else begin Select @so_maxOrders=max(F_orders) From T_class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%'; --得到自身系的最大排序号 Select @s_Childs= 1; Delete from T_Class Where F_Id=@ClsId; Update T_Class Set F_Orders = F_Orders - @s_childs where F_Type=@clsType And F_RootId=@O_RootId And F_Orders > @so_maxOrders; return 0 end END