MAXRECURSION not allowed in Inline Table-valued Function

[Jean-Nicolas BERGER]

Hello,
It seem's that the MAXRECURSION option is not allowed to complete a WITH in
an Inline Table-valued Function, whereas it is in a Multi-statement
function's code.
But I can't find any MSDN article about this.
Could someone help me?
Thx.
JN.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Inline_Function]') AND type in (N'FN', N'IF', N'TF',
N'FS',
N'FT'))
DROP FUNCTION [dbo].[Inline_Function]
GO
CREATE FUNCTION dbo.Inline_Function (@Max_Value int)
RETURNS TABLE
AS RETURN
(
WITH Numbers (_Value) as
(select 1
union all
select _Value+1
from Numbers
where _Value<@Max_Value
)
select _Value from Numbers
-- option (MAXRECURSION 0) -- Incorrect syntax near the keyword
'option'.
)
GO

[Itzik Ben-Gan]

That's right. The MAXRECURSION hint is not allowed in the query definition
of an inline function; but it is allowed in the query against the function:

select *
from Inline_Function(102) as f
option (maxrecursion 0);

BTW, if your question isn't generic, rather you are after a function that
returns a table of numbers, here's a solution that is much faster and also
doesn't exceed the default MAXRECURSION limit:

IF OBJECT_ID('dbo.fn_nums') IS NOT NULL
DROP FUNCTION dbo.Nums;
GO
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.

"Jean-Nicolas BERGER" <j-n.enleve...@club-internet.fr> wrote in
message news:O51$h7ZRHH...@TK2MSFTNGP03.phx.gbl...

[Jean-Nicolas BERGER]

Thanks for your great fn_nums function, it explains me a lot of thinks about
the way the CTE works.
Concerning the use of the MAXRECUSION when calling the Iniline-Function, I
thought abour it, but I'm surprised that no MSDN page seems to deal about
this aspect...
JN.

"Itzik Ben-Gan" <it...@REMOVETHIS.solidqualitylearning.com> a écrit dans le
message de news: 75407265-7A8D-4F3E...@microsoft.com...