CODEWITHSUNDEEP
sqlsql-servert-sql
DK ALT
DK ALT

Reputation: 2193

SQL Function for returning recursive value

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

Upvotes: 2

Views: 349

Answers (2)

SchmitzIT
SchmitzIT

Reputation: 9552

Your RETURNS part of the statement does not specify what it will return.

Upvotes: 0

roman
roman

Reputation: 117370

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

Upvotes: 3

Related Questions