SQL Function for returning recursive value

Question

I'm trying to create a function for returning a recursive value but I getting a syntax error.

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 specifying return type of the function, after that you have to define function body, like this:

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
)

here's an example - sql fiddle demo