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

//添加类别
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

GO

 

转载于:https://www.cnblogs.com/zyosingan/archive/2007/12/26/1015350.html