用于返回递归值的 SQL 函数

Question

我正在尝试创建一个返回递归值的函数，但出现语法错误。

CREATE FUNCTION getObs
(
    @obs int
)
RETURNS 
WITH ret2 AS(
    SELECT * 
    FROM OBS 
    WHERE OBS_Id = @obs 
    UNION ALL 
    SELECT t.* 
    FROM OBS as t INNER JOIN 
        ret2 r ON t.OBS_Id = r.UnitId
    )  
SELECT * 
FROM ret2 r
WHERE unity_id = 7

Answer 1

RETURNS指定函数的返回类型，之后你必须定义函数体，如下所示:

CREATE FUNCTION getObs
(
    @obs int
)
RETURNS table -- <-- returns table so it's a table function
as
return  -- <- here's actual return
(
    WITH ret2 AS(
        SELECT * 
        FROM OBS 
        WHERE OBS_Id = @obs 
        UNION ALL 
        SELECT t.* 
        FROM OBS as t INNER JOIN 
            ret2 r ON t.OBS_Id = r.UnitId

        )  
    SELECT * 
    FROM ret2 r
    WHERE unity_id = 7
)

这是一个例子 - sql fiddle demo