SQL Server返回遞迴結果集的函數

工作中有個遞迴很多地方都要用到，不想每個預存程序中都定義，但視圖裡不能定義變數，只好寫成函數了。

CREATE function [dbo].[F_GETDWData]
(
 @ID int
)
 returns @t_Level table (ID int,Level int) --根據所傳遞的ID，遞迴查詢所有下級單位元據。
begin
 DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM MM_DW
WHERE ID =@ID
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level
    FROM MM_DW a,@t_Level b
    WHERE a.SJID=b.ID
        AND b.Level=@Level-1
END   
 return     
end

調用方法很簡單：

SELECT * FROM F_GETDWData(@ID)

以上是SQL Server 2000中，遞迴得用手動迴圈的方式。

 

如果是在SQL Server 2005中，可以用下面的方式：

CREATE function [dbo].[F_GETDWData]
(
 @ID int
)
returns @t_Level table (ID int,Level int) --根據所傳遞的ID，遞迴查詢所有下級單位元據。

BEGIN

WITH ACT(ID,SJID)
AS
(
SELECT ID,SJID FROM RZEAM.MM_DW
WHERE ID=@ID
UNION ALL
SELECT A.ID,A.SJID
FROM RZEAM.MM_DW A,ACT
WHERE A.SJID=ACT.ID
)

INSERT @t_Level
SELECT ID,SJID FROM ACT

RETURN
END