添加类别和修改类别(无限级别分类)

//添加类别
CREATE PROCEDURE DS_AddNewsType
(
@NewsTypeName varchar(100),
@NewsTypeDesc varchar(500),
@ParentID int,

@MasterID int,
@ReleaseDate datetime,
@iLanguage tinyint
)
AS
DECLARE @RecordCount as int
SET @RecordCount=
(
select count(*) from DS_NewsType where NewsTypeName=@NewsTypeName
)

DECLARE @NewsTypeLevel as int
SET @NewsTypeLevel=
(
select NewsTypeLevel from DS_NewsType where NewsTypeID=@ParentID
)
if @NewsTypeLevel is null
begin
set @NewsTypeLevel=0
end

if @RecordCount<=0 begin
INSERT INTO
  DS_NewsType
  (
   NewsTypeName,
   NewsTypeDesc,
   ParentID,
   NewsTypeLevel,
   IDTree,

   MasterID,
   AddDate,
   ReleaseDate,
   iLanguage
  )
  VALUES
  (
   @NewsTypeName,
   @NewsTypeDesc,
   @ParentID,
   @NewsTypeLevel+1,
   '.',

   @MasterID,
   GetDate(),
   @ReleaseDate,
   @iLanguage
  )

DECLARE @IDTree as varchar(100)
SET @IDTree=
(
select IDTree from DS_NewsType where NewsTypeID=@ParentID
)

if @IDTree is null
  begin
   Update
      DS_NewsType
   Set
      IDTree = ltrim(str(@@Identity))+'.'
   WHERE
      NewsTypeID = @@Identity
  end
else
  begin
   Update
      DS_NewsType
   Set
      IDTree = @IDTree+ltrim(str(@@Identity))+'.'
   WHERE
      NewsTypeID = @@Identity
  end

Update
    DS_NewsType
Set
    IsLeaf = 0
WHERE
    NewsTypeID = @ParentID

end

RETURN @@Identity
GO

//修改类别

CREATE PROCEDURE DS_EditNewsType
(
@NewsTypeID int,
@NewsTypeName varchar(100),
@NewsTypeDesc varchar(500),
@ParentID int,

@MasterID int,
@ReleaseDate datetime,
@iLanguage tinyint
)
AS
DECLARE @RecordCount as int
SET @RecordCount=
(
select count(*) from DS_NewsType where NewsTypeName=@NewsTypeName and NewsTypeID!=@NewsTypeID
)

DECLARE @NewsTypeLevel as int
SET @NewsTypeLevel=
(
select NewsTypeLevel from DS_NewsType where NewsTypeID=@ParentID
)
if @NewsTypeLevel is null
begin
set @NewsTypeLevel=0
end

if @RecordCount<=0 begin
Update
    DS_NewsType
Set
    NewsTypeName = @NewsTypeName,
    NewsTypeDesc = @NewsTypeDesc,
    ParentID = @ParentID,
    NewsTypeLevel = @NewsTypeLevel+1,

    MasterID = @MasterID,
    ReleaseDate = @ReleaseDate,
    iLanguage = @iLanguage
WHERE
    NewsTypeID = @NewsTypeID

DECLARE @IDTree as varchar(100)
SET @IDTree=
(
  select IDTree from DS_NewsType where NewsTypeID=@ParentID
)

if @IDTree is null
  begin
   Update
      DS_NewsType
   Set
      IDTree = ltrim(str(@NewsTypeID))+'.'
   WHERE
      NewsTypeID = @NewsTypeID
  end
else
  begin
   Update
      DS_NewsType
   Set
      IDTree =convert(nvarchar(100),@ParentID)+'.'+ltrim(str(@NewsTypeID))+'.'
   WHERE
      NewsTypeID = @NewsTypeID
  end

Update
    DS_NewsType
Set
    IsLeaf = 0
WHERE
    NewsTypeID = @ParentID
end

RETURN