树形结构的数据库实现，存储过程实现添加删除查询功能

USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsDelete]    Script Date: 2015/11/19 23:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BbsDelete]
( 
    @ID INT 
)   
AS 
    DECLARE @LID AS INT, @RID AS INT, @WID AS INT, @DID AS INT, @NUM as int
set @NUM=0
    SET @DID = 0  
    SELECT @DID = ID, @LID = LID, @RID = RID, @WID = RID - LID + 1 FROM Bbs WHERE ID = @ID  
    IF @DID != 0  
    BEGIN 
select @NUM=@WID/2
        DELETE FROM Bbs WHERE LID BETWEEN @LID AND @RID

        UPDATE Bbs SET RID = RID - @WID WHERE RID > @RID
        UPDATE Bbs SET LID = LID - @WID WHERE LID > @RID  
update Bbs set ID=ID-@NUM where ID>@ID
update Bbs set FID=FID-@NUM where FID>@ID


    END 
    SELECT @DID  






USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsInsert]    Script Date: 2015/11/19 23:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   PROCEDURE [dbo].[BbsInsert]  
(  
    @Root INT,  
@tieName nvarchar(100),
@bbsTitle nvarchar(50),
@detail nvarchar(2000),
@pubTime nchar(19),
@publisherId INT,
@tp INT,
@did INT,
@state INT
)  
AS 
    DECLARE @LID AS INT,@RID AS INT,@NID AS INT,@Tree AS INT,@NkID AS INT,@CNT AS INT

SET @LID=0
    SET @RID = 1  
    SET @NID = 0  
    SET @Tree = 0  
SET @CNT=0



-- 表中为空，没有树根节点，则先插入树根节点
set @nkid=(select count(*)+1 from Bbs)
IF (@nkid=1)
BEGIN
INSERT INTO Bbs(id,LID,RID,Tree,fid,tieName,bbsTitle,detail,pubTime,publisherId,tp,did,state)      VALUES (0,1,2,0,-1,@tieName,@bbsTitle,@detail,@pubTime,@publisherId,@tp,@did,@state)  
END
ELSE
BEGIN


--在根节点下插入



SELECT @LID=LID,@RID = RID, @Tree = Tree + 1  
FROM Bbs WHERE ID = @Root  


SET @NID=@ROOT+(@RID-@LID+1)/2

UPDATE  Bbs  SET RID = RID + 2 WHERE RID  >= @RID 
UPDATE  Bbs  SET LID = LID + 2 WHERE LID > @RID  
UPDATE  Bbs  SET ID = ID + 1 WHERE LID > @RID
UPDATE  Bbs  SET fID = fID + 1 WHERE fid> @root AND RID>@RID


INSERT INTO Bbs(id,LID,RID,Tree,fid,tieName,bbsTitle,detail,pubTime,publisherId,tp,did,state)      VALUES (@NID,@RID,@RID + 1,@Tree,@root,@tieName,@bbsTitle,@detail,@pubTime,@publisherId,@tp,@did,@state)  

SET @NkID = SCOPE_IDENTITY()  

SELECT @NkID  


END





USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsSelect]    Script Date: 2015/11/19 23:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BbsSelect]
-- Add the parameters for the stored procedure here
@Root INT,
@Tree INT
AS
BEGIN


SELECT *
FROM Bbs AS Z,Bbs AS P
WHERE P.ID=@Root
AND Z.LID>=P.LID AND Z.RID <=P.RID
AND (@Tree = 0 OR Z.Tree<=P.Tree+@Tree)
ORDER BY Z.LID ASC
END