How to set the maxrecursion option for a CTE inside a Table-Valued-Function
Old issue but... I just wanted to clarify why OPTION(MAXRECURSION x)
is not allowed in an in-line table-valued function. This is because iTVF's get inlined when you use them in a query. And, as we all know, you cannot put this option anywhere else save at the very end of the query. This is THE reason it will never be possible to put it inside an iTVF (unless the parser and/or algebrizer does some magic behind the scenes, which I don't think it will any time soon). mTVF's (multi-statement table-valued functions) is a different story because they don't get inlined (and are so slow that they should never be used in queries; it's OK to use them in an assignment to a variable, though, but then again---beware of loops!).
Old thread, I know, but I needed the same thing and just dealt with it by using a multi-statement UDF:
CREATE FUNCTION DatesInRange
(
@DateFrom datetime,
@DateTo datetime
)
RETURNS
@ReturnVal TABLE
(
date datetime
)
AS
BEGIN
with DateTable as (
select dateFrom = @DateFrom
union all
select DateAdd(day, 1, df.dateFrom)
from DateTable df
where df.dateFrom < @DateTo
)
insert into @ReturnVal(date)
select dateFrom
from DateTable option (maxrecursion 32767)
RETURN
END
GO
There are probably efficiency issues with this, but I can afford it in my case.
From this MSDN forums thread I learn that
[the]
OPTION
clause can be used only at the statement levelSo you cannot use it within a query expression inside view definitions or inline TVFs etc. The only way to use it in your case is to create the TVF without the
OPTION
clause and specify it in the query that uses the TVF. We have a bug that tracks request for allowing use ofOPTION
clause inside any query expression (for example,if exists()
or CTE or view).
and further
You can not change the default value of that option inside a udf. You will have to do it in the statement referencing the udf.
So in your example, you must specify the OPTION
when you call your function:
CREATE FUNCTION [liste_jour]
(@debut date,@fin date)
RETURNS TABLE
AS
RETURN
(
WITH CTE as(
SELECT @debut as jour
UNION ALL
SELECT DATEADD(day, 1, jour)
FROM CTE
WHERE DATEADD(day, 1, jour) <= @fin)
SELECT jour FROM CTE -- no OPTION here
)
(later)
SELECT * FROM [liste_jour] ( @from , @to ) OPTION ( MAXRECURSION 365 )
Note that you can't work round this by having a second TVF that just does the above line - you get the same error, if you try. "[the] OPTION
clause can be used only at the statement level", and that's final (for now).
A little creative use of CTEs and cartesian products (cross joins) will get you around the MAXRECURSION
limit of 100. 3 CTEs with a limit of 4 records on the last one nets you 40,000 records, which will be good for more than 100 years worth of data. If you expect more difference between @debut and @fin, you can adjust cte3
.
-- please don't SHOUTCASE your SQL anymore... this ain't COBOL
alter function liste_jour(@debut date, @fin date) returns table as
return (
with cte as (
select 0 as seq1
union all
select seq1 + 1
from cte
where seq1 + 1 < 100
),
cte2 as (
select 0 as seq2
union all
select seq2 + 1
from cte2
where seq2 + 1 < 100
),
cte3 as (
select 0 as seq3
union all
select seq3 + 1
from cte3
where seq3 + 1 <= 3 -- increase if 100 years isn't good enough
)
select
dateadd(day, (seq1 + (100 * seq2) + (10000 * seq3)), @debut) as jour
from cte, cte2, cte3
where (seq1 + (100 * seq2) + (10000 * seq3)) <= datediff(day, @debut, @fin)
)
go
-- test it!
select * from liste_jour('1/1/2000', '2/1/2000')