--获得某一分类下的所有子分类
1
CREATE 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
CREATE PROCEDURE sp_Market_News_Type_GetChildType2
@pTypeID int3
AS4
DECLARE @RecCount int5
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 = @pTypeID10
SELECT @RecCount = COUNT(1) FROM @ChildTable11
12
WHILE @RecCount > 013
BEGIN14
--把子结点插入到结果表中15
INSERT INTO @AllTable SELECT OID FROM @ChildTable16
17
DELETE FROM @TempTable18
INSERT INTO @TempTable SELECT OID FROM @ChildTable19
DELETE FROM @ChildTable20
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 @ChildTable24
END25
26
SELECT OID FROM @AllTable27
GO 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
*/5
CREATE PROCEDURE sp_Market_News_Type_IsChild 6
@pParentID int,7
@pChildID int,8
@poutResult char(1) output9
AS10
DECLARE @RecCount int11
SELECT @RecCount = 012
13
SELECT @poutResult = '0'14
15
WHILE @pChildID > 016
BEGIN17
SELECT @RecCount = COUNT(1) FROM tb_Market_News_Types AS T WHERE T.ID = @pChildID AND T.ParentID = @pParentID18
IF @RecCount > 0 19
BEGIN20
SELECT @poutResult = '1'21
BREAK22
END23
ELSE24
BEGIN25
SELECT @pChildID = ParentID FROM tb_Market_News_Types AS T WHERE T.ID = @pChildID26
END 27
END28
GO
