--获得某一分类下的所有子分类
1CREATE PROCEDURE sp_Market_News_Type_GetChildType
2    @pTypeID int
3AS
4    DECLARE @RecCount int
5    DECLARE @AllTable table( OID int )
6    DECLARE @ChildTable table( OID int )
7    DECLARE @TempTable table( OID int )
8
9    INSERT INTO @ChildTable SELECT ID FROM TB_Market_News_Types WHERE ParentID = @pTypeID
10    SELECT @RecCount = COUNT(1) FROM @ChildTable
11
12    WHILE @RecCount > 0
13    BEGIN
14        --把子结点插入到结果表中
15        INSERT INTO @AllTable SELECT OID FROM @ChildTable
16
17        DELETE FROM @TempTable
18        INSERT INTO @TempTable SELECT OID FROM @ChildTable
19        DELETE FROM @ChildTable
20
21        INSERT INTO @ChildTable SELECT ID FROM tb_Market_News_Types WHERE ParentID IN ( SELECT OID FROM @TempTable )   
22       
23        SELECT @RecCount = COUNT(1) FROM @ChildTable
24    END
25
26    SELECT OID FROM @AllTable
27GO
1/**//*
2    判断@pChildID是否为@pParentID的子分类
3    return '0'为否,'1'为是
4*/
5CREATE PROCEDURE sp_Market_News_Type_IsChild
6    @pParentID int,
7    @pChildID int,
8    @poutResult char(1) output
9AS
10    DECLARE @RecCount int
11    SELECT @RecCount = 0
12
13    SELECT @poutResult = '0'
14
15    WHILE @pChildID > 0
16    BEGIN
17        SELECT @RecCount = COUNT(1) FROM tb_Market_News_Types AS T WHERE T.ID = @pChildID AND T.ParentID = @pParentID
18        IF @RecCount > 0
19        BEGIN
20            SELECT @poutResult = '1'
21            BREAK
22        END
23        ELSE
24        BEGIN
25            SELECT @pChildID = ParentID FROM tb_Market_News_Types AS T WHERE T.ID = @pChildID
26        END   
27    END
28GO