Comment définir l'option maxrecursion pour un CTE à l'intérieur d'une Table-Valued-Function ?

Question

Je suis confronté à un problème de déclaration de l'option maxrecursion pour un CTE dans un TVF.

Voici le CTE (un simple calendrier) :

DECLARE @DEBUT DATE = '1/1/11',   @FIN DATE = '1/10/11';

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 option (maxrecursion 365)

et le TVF :

 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
  --option (maxrecursion 365)
 )

Le TVF ci-dessus fonctionne correctement sans l'option maxrecursion. mais il y a une erreur de syntaxe avec l'option. Quelle est la solution ?

Answer 1

De ce fil de discussion sur les forums MSDN J'apprends que

[le] OPTION ne peut être utilisée qu'au niveau de la déclaration

Vous ne pouvez donc pas l'utiliser dans une expression de requête, dans des définitions de vues ou des TVF en ligne, etc. La seule façon de l'utiliser dans votre cas est de créer le TVF sans l'option OPTION et la spécifier dans la requête qui utilise le TVF. Nous avons un bogue qui suit la requête pour permettre l'utilisation de la clause OPTION à l'intérieur de toute expression de requête (par exemple, if exists() ou CTE ou vue).

et en outre

Vous ne pouvez pas changer la valeur par défaut de cette option dans un udf. Vous devrez le faire dans l'instruction faisant référence à l'udf.

Ainsi, dans votre exemple, vous devez spécifier le OPTION quand vous appelez votre fonction :

 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
 )

(plus tard)

SELECT * FROM [liste_jour] ( @from , @to ) OPTION ( MAXRECURSION 365 )

Notez que vous ne pouvez pas contourner ce problème en ayant une deuxième TVF qui ne fait que la ligne ci-dessus - vous obtenez la même erreur, si vous essayez. "[le] OPTION ne peut être utilisée qu'au niveau de la déclaration", et c'est tout (pour l'instant).

Answer 2

Vieux sujet, je sais, mais j'ai eu besoin de la même chose et j'ai fait face à la situation en utilisant un UDF multi-statement :

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

Cela pose probablement des problèmes d'efficacité, mais je peux me le permettre dans mon cas.

Answer 3

Vieux problème mais... Je voulais juste clarifier pourquoi OPTION(MAXRECURSION x) n'est pas autorisé dans une fonction à valeur de tableau en ligne. Cela est dû au fait que la fonction iTVF être mis en ligne lorsque vous les utilisez dans une requête. Et, comme nous le savons tous, vous ne pouvez pas placer cette option ailleurs qu'à la toute fin de la requête. C'est LE Les mTVF (multi-statement table-valued functions) sont une autre histoire parce qu'elles ne sont pas inlined (et sont si lentes qu'elles ne devraient jamais être utilisées dans des requêtes ; il est possible de les utiliser dans une affectation à une variable, mais là encore, attention aux boucles !

Answer 4

Un peu d'utilisation créative des CTE et des produits cartésiens (jointures croisées) vous permettra de contourner l'obstacle de l'accès à l'information. MAXRECURSION limite de 100. 3 CTE avec une limite de 4 enregistrements sur le dernier vous permettent d'obtenir 40 000 enregistrements, ce qui correspond à plus de 100 ans de données. Si vous vous attendez à une plus grande différence entre @debut et @fin, vous pouvez ajuster les paramètres suivants 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')

Answer 5

Une autre façon de traiter ce problème consiste à le décomposer en une paire de CTE, dont aucun n'atteint la limite de récurrence de 100. Le premier CTE crée une liste avec la date de début pour chaque mois de la plage. Le deuxième CTE remplit ensuite tous les jours de chaque mois. Tant que la plage d'entrée est inférieure à 100 mois, cela devrait fonctionner correctement. Si une plage d'entrée de plus de 100 mois est requise, la même idée pourrait être étendue avec un troisième CTE pour les années ajouté avant le CTE des mois.

CREATE FUNCTION [liste_jour]    
(@debut datetime, @fin datetime)    
RETURNS TABLE   
AS      
RETURN          
(   
    WITH CTE_MOIS AS
    (           
        SELECT JOUR_DEBUT = @debut
        UNION ALL
        SELECT DATEADD(MONTH, 1, CTE_MOIS.JOUR_DEBUT)
          FROM CTE_MOIS         
         WHERE DATEADD(MONTH, 1, CTE_MOIS.JOUR_DEBUT) <= @fin
    ),

    CTE_JOUR AS
    (           
        SELECT JOUR = CTE_MOIS.JOUR_DEBUT
          FROM CTE_MOIS
        UNION ALL           
        SELECT DATEADD(DAY, 1, CTE_JOUR.JOUR)
          FROM CTE_JOUR
         WHERE MONTH(CTE_JOUR.JOUR) = MONTH(DATEADD(DAY, 1, CTE_JOUR.JOUR)) AND
            DATEADD(DAY, 1, CTE_JOUR.JOUR) <= @FIN
    )

    SELECT JOUR
      FROM CTE_JOUR
)