表设计如上:
此表需求只需要分三级,编号为六位,如顶级100000,二级100100,三级100101
添加分类存储如下:
CREATE PROCEDURE [dbo].[UP_ArticleTypes_Add](
@F_Id int output, --返回添加类型ID
@F_ParentId int, --添加到哪个类型下边
@F_Name varchar(20), --类型名称
@F_IcoPath varchar(100) --图标
)
as
Set @F_Id =0
Declare @F_SortIndex int --排序ID
If(@F_Name ='')
Begin Raiserror('分类名称不能为空!',16,1) Return 0 End
If Exists(select top 1 F_ID From T_ArticleTypes where (@F_ParentId/10000=F_Id/10000 And @F_ParentId%10000=0 And F_Name=@F_Name) or (@F_ParentId/100=F_Id/100 And @F_ParentId%100=0 And @F_ParentId%1000>0 And F_Name=@F_Name))
Begin Raiserror('存在同类型同名称分类!',16,1) Return 2 End
-------判断一级分类
If(@F_ParentId=0)
Begin
Select @F_Id=IsNull(Max(F_Id),0),@F_SortIndex=IsNull(MAX(F_SortIndex),0) From [T_ArticleTypes] Where F_ID%10000=0
Set @F_Id=@F_Id+100000
Set @F_SortIndex=@F_SortIndex+1
End
-------添加二级分类
If(@F_ParentId%100000=0 And @F_ParentId<>0)
Begin
Select @F_Id=IsNull(Max(F_Id),@F_ParentId),@F_SortIndex=IsNull(MAX(F_SortIndex),0) from [T_ArticleTypes] where F_ID/10000=@F_ParentId/10000 and F_ID%100=0 and F_ID%10000>0
Set @F_Id=@F_Id+100
Set @F_SortIndex=@F_SortIndex+1
End
-------添加三级分类
If(@F_ParentId%100=0 And @F_ParentId%100000<>0)
Begin
Select @F_Id=IsNull(Max(F_Id),@F_ParentId),@F_SortIndex=IsNull(MAX(F_SortIndex),0) from [T_ArticleTypes] where F_ID/10000=@F_ParentId/10000 and F_ID%100>0 And LEFT(F_ID,4)=LEFT(@F_ParentId,4)
Set @F_Id=@F_Id+1
Set @F_SortIndex=@F_SortIndex+1
End
--添加文章分类
Insert [T_ArticleTypes](F_ID,F_Name,F_IcoPath,F_SortIndex)
Values(@F_ID,@F_Name,@F_IcoPath,@F_SortIndex)
SET @F_Id = @@Identity
If(@F_Id =0)
Begin Raiserror('添加失败!',16,1) Return 0 End
Return 1
go
编辑分类:
Create procedure [dbo].[UP_ArticleTypes_Update](
@F_Id int, --分类ID
@F_ParentID int, --父级ID
@F_Name varchar(20), --分类名称
@F_IcoPath varchar(100) --图标
)
AS
Declare @F_SortIndex int
Declare @NewID int
--验证是否存在相同名称
If Exists(select top 1 F_ID From T_ArticleTypes where (@F_ParentId/10000=F_Id/10000 And @F_ParentId%10000=0 And F_Name=@F_Name) or (@F_Pa