Code
1alter FUNCTION f_Cid(@ResultID int)
2 RETURNS @t_Level TABLE(ResultID char(3),Level int)
3 AS
4 BEGIN
5 DECLARE @Level int
6 SET @Level=1
7 INSERT @t_Level SELECT @ResultID,@Level
8 WHILE @@ROWCOUNT>0
9 BEGIN
10 SET @Level=@Level+1
11 INSERT @t_Level SELECT a.PARENTID,@Level
12 FROM D_COMPANYINFOType a,@t_Level b --这"D_COMPANYINFOType"要换成自己的递归表
13 WHERE a.D_CHILDID=b.ResultID --- “D_CHILDID”换成自己的递归表的 父ID
14 AND b.Level=@Level-1
15 END
16 RETURN
17 END
18 GO
19/**//*测试语句*/
20 SELECT *
21 FROM f_Cid(4) where level>1
22
23--where Level>1
24
25 /**//*--结果 */
26
27
28
1alter FUNCTION f_Cid(@ResultID int)
2 RETURNS @t_Level TABLE(ResultID char(3),Level int)
3 AS
4 BEGIN
5 DECLARE @Level int
6 SET @Level=1
7 INSERT @t_Level SELECT @ResultID,@Level
8 WHILE @@ROWCOUNT>0
9 BEGIN
10 SET @Level=@Level+1
11 INSERT @t_Level SELECT a.PARENTID,@Level
12 FROM D_COMPANYINFOType a,@t_Level b --这"D_COMPANYINFOType"要换成自己的递归表
13 WHERE a.D_CHILDID=b.ResultID --- “D_CHILDID”换成自己的递归表的 父ID
14 AND b.Level=@Level-1
15 END
16 RETURN
17 END
18 GO
19/**//*测试语句*/
20 SELECT *
21 FROM f_Cid(4) where level>1
22
23--where Level>1
24
25 /**//*--结果 */
26
27
28