第一個範例:尋找比指定員工高 N 層的管理員。可用遞歸或迴圈。先看遞歸實現 (Listing 2)。
CREATE FUNCTION dbo.ufn_GetAncestor
(
@empid AS int,
@lvl AS int = 1 -- 高於指定員工的層級數
)
RETURNS int
AS
BEGIN
IF @lvl IS NULL OR @empid IS NULL OR @lvl < 0 RETURN NULL
IF @lvl = 0 RETURN @empid
RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @empid),
@lvl - 1)
END
函數先檢查參數合法性,再以 @lvl=0 為終止條件,遞歸調用自身,每次向上一層。遞歸有兩大限制:
為突破 32 層限制,提供迴圈版本 (Listing 3)。
CREATE FUNCTION dbo.ufn_GetAncestor2
(
@empid AS int,
@lvl AS int = 1
)
RETURNS int
AS
BEGIN
IF @lvl IS NULL OR @empid IS NULL OR @lvl < 0 RETURN NULL
DECLARE @mgrid AS int
SET @mgrid = @empid
WHILE @lvl > 0 AND @mgrid IS NOT NULL
SELECT @mgrid = mgrid, @lvl = @lvl - 1
FROM Employees WHERE empid = @mgrid
RETURN @mgrid
END
SELECT dbo.ufn_GetAncestor(11, 2) → 返回 Janet (empid=3)。SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)。LEFT OUTER JOIN 確保無管理員的員工也被列出。