MS SQL Server
  1. MS SQL Server
  3. Comment faire pour configurer l'option maxrecursion pour un CTE dans une fonction table-valeur
Intereting Posts
playframework 2.X JPA à MSSQL Impossible de build Hibernate SessionFactory Comment implémenter une réplication de model d'abonné central? Index non clusterisé avec include vs presque identique Index non clusterisé sans; Couverture de requête multiple Existe-t-il un moyen simple de déterminer la cardinalité d'une relation de key étrangère en utilisant SMO? Comment puis-je mettre à jour les loggings dans sql en utilisant d'autres loggings comme valeurs? Ordre de sorting par défaut pour une requête de sélection dans SQL Server 2005 et SQL Server 2012 PHP + ODBC + FreeTDS + MSSQL: Impossible de changer la version de TDS UPDATE conditionnel T-SQL (v2) Spécifiez un nom d'user SQL autre que dbo dans Code First Entity Framework (C # ASP.NET MVC 3) NHibernate: Configure () dans chaque request de page? Somme SQL de tous les loggings et dernier logging de chaque user Comment puis-je améliorer les opérations de lecture sur une grande table? SSIS Conditional Split NULL gestion Comptage du nombre d'enfants dans datatables SQL hiérarchiques Analyser datatables XML dans SQL Server 2012

Comment faire pour configurer l'option maxrecursion pour un CTE dans une fonction table-valeur

Je suis confronté à un problème pour déclarer l'option maxrecursion pour un CTE à l'intérieur d'un TVF

voici le CTE (un simple calendar): 

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 [list_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 bien sans l'option maxrecursion mais il y a une erreur de syntaxe avec l'option. quelle est la solution ?

Cordialement

    À partir de ce fil de discussion MSDN, j'apprends que

    La clause OPTION ne peut être utilisée qu'au niveau de l'instruction

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

    et plus loin

    Vous ne pouvez pas modifier la valeur par défaut de cette option dans un file udf. Vous devrez le faire dans la déclaration référençant le file udf.

    Donc, dans votre exemple, vous devez spécifier l' OPTION lorsque vous appelez votre fonction: 

      CREATE FUNCTION [list_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 [list_jour] ( @from , @to ) OPTION ( MAXRECURSION 365 )

    Notez que vous ne pouvez pas contourner cela en ayant un second TVF qui fait juste la ligne ci-dessus – vous obtenez la même erreur, si vous essayez. "[La] clause OPTION ne peut être utilisée qu'au niveau de la déclaration", et c'est définitif (pour le moment).

    Ancien fil, je sais, mais j'avais besoin de la même chose et je l'ai juste traité en utilisant une UDF multi-instructions: 

     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

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

    Une autre façon de gérer cela est de split le problème en deux CTE, dont aucun n'atteint la limite de récursivité de 100. Le premier CTE crée une list avec la date de début pour chaque mois de la plage. Le second CTE se remplit alors tous les jours de chaque mois. Tant que la plage d'input est inférieure à 100 mois, cela devrait fonctionner correctement. Si une plage d'input supérieure à 100 mois est requirejse, la même idée pourrait être étendue avec un troisième CTE pour les années ajoutées avant les mois CTE. 

     CREATE FUNCTION [list_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 )

    Ancien problème mais … Je voulais juste clarifier pourquoi OPTION (MAXRECURSION x) n'est pas autorisée dans une fonction table en ligne. C'est parce que les iTVF sont embeddeds lorsque vous les utilisez dans une requête. Et, comme nous le soaps tous, vous ne pouvez mettre cette option ailleurs qu'à la toute fin de la requête. C'est LA raison pour laquelle il ne sera jamais possible de le mettre à l'intérieur d'un iTVF (à less que l'parsingur et / ou algebrizer ne fasse de la magie dans les coulisses, ce qui ne sera pas le cas sous peu). Les mTVF (fonctions table à valeurs multiples) sont une histoire différente parce qu'elles ne sont pas embeddedes (et sont si lentes qu'elles ne devraient jamais être utilisées dans les requêtes, mais il est correct de les utiliser dans une affectation à une variable, mais encore une fois — méfiez-vous des loops!).

    Un peu d'utilisation créative des CTE et des produits cartésiens (jointures croisées) vous permettra de contourner la limite de MAXRECURSION de 100. 3 CTE avec une limite de 4 loggings sur le dernier vous filera 40 000 loggings, ce qui sera bon pour plus de 100 ans de données. Si vous attendez plus de différence entre @debut et @fin, vous pouvez ajuster cte3 . Aussi, s'il vous plaît, arrêtez de CRIER votre SQL. 

     -- please don't SHOUTCASE your SQL anymore... this ain't COBOL alter function list_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 list_jour('1/1/2000', '2/1/2000')