增加，编纂，删除操纵有点贫苦。。我是间接用存储历程的。。不晓得人人能看得懂不。。哈哈。 1、增加分类(Category_Add) 复制代码代码以下:
CREATEproc[dbo].[Category_Add]
@CategoryNamenvarchar(50),
@BindCategoryIDint,
@CategoryIDintoutput
as
declare@Successbit
set@Success=1
--天生不反复的CategoryID
declare@ibit
set@i=0
while@i=0
begin
set@CategoryID=LEFT(10000000+CONVERT(bigint,ABS(CHECKSUM(NEWID()))),8)
if(notexists(selectCategoryIDfromtomi_CategorywhereCategoryID=@CategoryID))
set@i=1
end
--拔出
BEGINTRAN
insertintotomi_Category(categoryID,CategoryName,Depth)values(@CategoryID,@CategoryName,@Depth)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)values(@CategoryID,@CategoryID,@Depth)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)select@CategoryID,BindCategoryID,Depthfromtomi_CategoryBindwhereCategoryID=@BindCategoryID
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END
COMMITTRAN
2、编纂修正分类(Category_Edit) 复制代码代码以下:
CREATEproc[dbo].[Category_Edit]
@CategoryIDint,
@CategoryNamenvarchar(50),
@BindCategoryIDint
as
--更新
BEGINTRAN
updatetomi_CategorysetCategoryName=@CategoryNamewhereCategoryID=@CategoryID
IF@@ERROR0
BEGIN
ROLLBACKTRAN
return0
END
COMMITTRAN
--检测是不是变动了下级目次
declare@isbit
set@is=0
if(exists(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDandBindCategoryID=@BindCategoryIDandDepth=(selectDepth-1fromtomi_CategorywhereCategoryID=@CategoryID)))
set@is=1
print@is
--变动了深度
if(@is=0)
BEGIN
--失掉下级目次的depth
declare@depthint
set@depth=0
select@depth=depthfromtomi_CategorywhereCategoryID=@BindCategoryID
set@depth=@depth+1
--print@depth
--变动子目次
declare@iint
declare@sCategoryIDint
declare@sBindCategoryIDint
declare@tCategoryIDListTable
(
CategoryIDint,
FlagIDtinyint
)
insert@tCategoryIDListselectc.CategoryID,0fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.BindCategoryID=@CategoryIDorderbyc.Depth
set@i=1
set@sBindCategoryID=@BindCategoryID
declare@errsint
set@errs=0
BEGINTRAN
while(@i>=1)
BEGIN
select@sCategoryID=0
selectTop1@sCategoryID=CategoryIDfrom@tCategoryIDListwhereFlagID=0
set@i=@@RowCount
--print@sCategoryID
if@sCategoryID>0
BEGIN
--删除，更新
deletefromtomi_CategoryBindwhereCategoryID=@sCategoryID
set@errs=@errs+@@error
updatetomi_Categorysetdepth=@depthwhereCategoryID=@sCategoryID
set@errs=@errs+@@error
--拔出
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)values(@sCategoryID,@sCategoryID,@Depth)
set@errs=@errs+@@error
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)select@sCategoryID,BindCategoryID,Depthfromtomi_CategoryBindwhereCategoryID=@sBindCategoryID
set@errs=@errs+@@error
set@sBindCategoryID=@sCategoryID
set@Depth=@Depth+1
--print@sCategoryID
--print@sBindCategoryID
--print@Depth
--print"--"
END
update@tCategoryIDListsetFlagID=1whereCategoryID=@sCategoryID
END
if(@errs>0)
BEGIN
ROLLBACKTRAN
return0
END
else
COMMITTRAN
END
3、删除分类(Category_Del)会间接删除子分类 复制代码代码以下:
createprocCategory_Del
@CategoryIDint
as
BEGINTRAN
deletefromtomi_CategorywhereCategoryIDin(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDorBindCategoryID=@CategoryID)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
return0
END
deletefromtomi_CategoryBindwhereCategoryIDin(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDorBindCategoryID=@CategoryID)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
return0
END
COMMITTRAN
4、分类列表，显现分类(Category_List)
复制代码代码以下:
CREATEprocCategory_List
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.Depth=1orderbyb.BindCategoryID,c.Depth
5、下级子分类列表(Category_upTree) 复制代码代码以下:
CreateProcCategory_UpTree
@CategoryIDint
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.BindCategoryIDwhereb.CategoryID=@CategoryIDorderbyc.Depth
GO
6、上级子分类列表(Category_downTree) 复制代码代码以下:
CreateProcCategory_DownTree
@CategoryIDint
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.BindCategoryID=@CategoryIDorderbyc.Depth
GO
