Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions

Hi!

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

Here is the function:


create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select


id, tbl_id, 0 lvl

from


tbl

where


id = @p_id

union all

select


t.id, t.tbl_id, lvl + 1
from

tbl_cte
inner join tbl t


on rnr.tbl_id = tbl_cte.id

)

select


id, tbl_id, lvl

from


tbl_cte

option (maxrecursion 0)

)


Please help!

Alexander.


P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.