--获得某一分类下的所有子分类
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
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
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