SQL recursive functions list all child levels of the parent level (ID parentid Mode)

1. -- Call method:
2. -- Select * From getchild ('24 ')
3. -- Select ID from getchild ('24 ')
4. -- Select * From kucun where producttype in (select ID from getchild ('24 '))
6. Create Function [DBO]. [getchild] (@ ID varchar (10 ))
7. Returns @ t table (ID varchar (10), parentid varchar (10), Level INT)
8. As
9. Begin
10. Declare @ I int
11. Set @ I = 1
12. Insert into @ t select @ ID, @ ID, 0 -- current level, current level. If not, comment out or add another parameter to select an operation.
13. Insert into @ t select ID, parentid, @ I from Dept where parentid = @ ID
15. While @ rowcount <> 0
16. Begin
17. Set @ I = @ I + 1
18. Insert into @ t
19. Select
20. A. ID, A. parentid, @ I
21. From
22. Dept a, @ T B
23. Where
24. A. parentid = B. ID and B. Level = @ I-1
25. End
26. Return
27. End
29. --------------------------------------------------------------------------------
30. -- In SQL server2005, the CTE [Public table expression] is provided to implement recursion:
31. Declare @ ID int
32. Set @ ID = 24; --- modify the parent node here
34. With rootnodecte (ID, parentid)
35. As
36. (
37. Select ID, parentid from Dept where parentid in (@ ID)
38. Union all
39. Select Dept. ID, Dept. parentid from rootnodecte
40. Inner join Dept
41. On rootnodecte. ID = Dept. parentid
42. )
44. Select * From rootnodecte
46. --------------------------------------------------------------------------------
47. --------------------------------------------------------------------------------
48. --------------------------------------------------------------------------------
50. -- Generate Test Data
51. Create Table dept (ID int, parentid int, MSG varchar (20 ))
52. Insert into dept select 1, 0, null
53. Insert into dept select 2, 1, null
54. Insert into dept select 3, 1, null
55. Insert into dept select 4, 2, null
56. Insert into dept select 5, 3, null
57. Insert into dept select 6, 5, null
58. Insert into dept select 7,6, null
59. Go
62. -- Create a user-defined function
63. Create Function [DBO]. [getchild] (@ ID varchar (10 ))
64. Returns @ t table (ID varchar (10), parentid varchar (10), Level INT)
65. As
66. Begin
67. Declare @ I int
68. Set @ I = 1
69. Insert into @ t select @ ID, @ ID, 0 -- current level, current level. If not, comment out or add another parameter to select an operation.
70. Insert into @ t select ID, parentid, @ I from Dept where parentid = @ ID
72. While @ rowcount <> 0
73. Begin
74. Set @ I = @ I + 1
75. Insert into @ t
76. Select
77. A. ID, A. parentid, @ I
78. From
79. Dept a, @ T B
80. Where
81. A. parentid = B. ID and B. Level = @ I-1
82. End
83. Return
84. End
87. -- Execute Query
88. Select ID from DBO. getchild (3)
89. Go
91. -- Output result
92. /*
93. 3
94. 5
95. 6
96. 7
97. */
99. -- Delete test data
100. Drop function getchild
101. Drop table Dept
103. Http://topic.csdn.net/u/20080409/16/1fb7d941-b1a1-4326-a936-230ddf057cbe.html