T-SQL存储过程:类别链提取
提取类别链
表结构:
PCID,CategoryName,ParentID
1 衬衫 0
2 裤子 0
3 长袖衬衫 1
1
set ANSI_NULLS ON
2
set QUOTED_IDENTIFIER ON
3
go
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
/* 创建:***
6
* 时间:200*/0*/16
7
* 用途:获取从根结点到叶结点的类别链条
8
*/
9
CREATE PROCEDURE [dbo].[GetCategorys]
10
AS
11
DECLARE
12
@CACHE_PCID int, --缓存PCID
13
@OUT_STR nvarchar(500), --输出的类别链条
14
@CACHE_PARENTID int, --缓存的父类别ID
15
@CACHE_CATENAME nvarchar(20) --缓存的类别名字
16
BEGIN
17
--获取所有独立叶结点类别
18
---------------------------------------------------------------------------
19
DECLARE ENDPOINT_CURSOR CURSOR FOR
20
select C.PCID from
21
(
22
select distinct PCID from w_ProductCategorys
23
) C
24
left outer join
25
(
26
--#PID带子类别的类别
27
select distinct B.ParentID from
28
(
29
select PCID,ParentID from w_ProductCategorys
30
) A
31
right outer join
32
(
33
select PCID,ParentID from w_ProductCategorys
34
) B
35
on A.PCID=B.ParentID where A.PCID is not null
36
--#PID
37
) D
38
on C.PCID=D.ParentID where D.ParentID is null
39
---------------------------------------------------------------------------
40
--临时表
41
SELECT PCID,ParentID,CategoryName INTO #Temp_TAB FROM w_ProductCategorys
42
--打开游标
43
OPEN ENDPOINT_CURSOR;
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
FETCH NEXT FROM ENDPOINT_CURSOR INTO @CACHE_PCID;
46
--游标循环
47
WHILE @@FETCH_STATUS=0
48
BEGIN
49
--循环分析PCID、ParentID,获取类别链条
50
--获取父类别
51
SET @CACHE_PARENTID=(SELECT ParentID FROM #Temp_TAB);
52
--获取叶结点类别名
53
SET @OUT_STR=(SELECT CategoryName FROM #Temp_TAB WHERE PCID=@CACHE_PCID);
54
--开始循环
55
WHILE @CACHE_PARENTID!=0
56
BEGIN
57
SET @CACHE_CATENAME=(SELECT CategoryName From #Temp_TAB WHERE PCID=@CACHE_PARENTID);
58
SET @OUT_STR=@CACHE_CATENAME+'----'+@OUT_STR;
59
SET @CACHE_PARENTID=(SELECT ParentID FROM #Temp_TAB WHERE PCID=@CACHE_PARENTID);
60
END
61
62
PRINT(@OUT_STR);
63
SET @OUT_STR='';
64
65
FETCH NEXT FROM ENDPOINT_CURSOR INTO @CACHE_PCID;
66
END
67
--关闭游标
68
CLOSE ENDPOINT_CURSOR;
69
DEALLOCATE ENDPOINT_CURSOR
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70END