sqlserver另类非递归的无限级分类(存储过程版)

复制代码 代码如下:

CREATE proc [dbo].[Category_Add]
@CategoryName nvarchar(50),
@BindCategoryID int,
@CategoryID int output
as
declare @Success bit
set @Success=1

--生成不重复的CategoryID
declare @i bit
set @i=0
while @i=0
begin
set @CategoryID=LEFT(10000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
if(not exists(select CategoryID from tomi_Category where CategoryID=@CategoryID))
set @i=1
end


--得到depth
declare @depth int
set @depth=0
select @depth=depth from tomi_Category where CategoryID=@BindCategoryID
set @depth=@depth+1

--插入
BEGIN TRAN
insert into tomi_Category(categoryID,CategoryName,Depth) values(@CategoryID,@CategoryName,@Depth)
if(@@ERROR<>0)
BEGIN
ROLLBACK TRAN
set @Success=0
END

insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) values(@CategoryID,@CategoryID,@Depth)
if(@@ERROR<>0)
BEGIN
ROLLBACK TRAN
set @Success=0
END

insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select @CategoryID,BindCategoryID,Depth from tomi_CategoryBind where CategoryID=@BindCategoryID
if(@@ERROR<>0)
BEGIN
ROLLBACK TRAN
set @Success=0
END
COMMIT TRAN

print @CategoryID