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
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
Your RETURNS part of the statement does not specify what it will return.
