sql server - recursive - Cómo configurar la opción maxrecursion para un CTE dentro de una función con valores de tabla

with sql oracle

Question

Me enfrenta un problema para declarar la opción maxrecursion para un CTE dentro de un TVF

aquí está el CTE (un calendario simple):

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)

y el 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)
 )

el TVF anterior se está ejecutando bien sin la opción maxrecursion, pero hay un error de sintaxis con la opción. cuál es la solución ?

Saludos

Answer 1

De este hilo de foros de MSDN , aprendo que

[la] cláusula OPTION se puede usar solo en el nivel de declaración

Por lo tanto, no puede usarlo dentro de una expresión de consulta dentro de definiciones de vista o TVF en línea, etc. La única forma de usarlo en su caso es crear el TVF sin la cláusula OPTION y especificarlo en la consulta que usa el TVF. Tenemos un error que rastrea la solicitud para permitir el uso de la cláusula OPTION dentro de cualquier expresión de consulta (por ejemplo, if exists() o CTE o vista).

y además

No puede cambiar el valor predeterminado de esa opción dentro de un udf. Tendrás que hacerlo en la declaración que hace referencia al udf.

Por lo tanto, en su ejemplo, debe especificar la OPTION cuando llame a su función:

 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
 )

(luego)

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

Tenga en cuenta que no puede evitar esto teniendo un segundo TVF que solo hace la línea anterior; si lo intenta, obtendrá el mismo error. "[la] cláusula OPTION se puede usar solo en el nivel de declaración", y eso es definitivo (por ahora).

Answer 2

Hilo viejo, lo sé, pero necesitaba lo mismo y lo solucioné usando un UDF de instrucciones múltiples:

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

Probablemente haya problemas de eficiencia con esto, pero puedo pagarlo en mi caso.

Answer 3

Otra forma de manejar esto es dividir el problema en un par de CTE, ninguno de los cuales alcanza el límite de recursión de 100. El primer CTE crea una lista con la fecha de inicio de cada mes en el rango. El segundo CTE se completa todos los días de cada mes. Siempre que el rango de entrada sea inferior a 100 meses, debería funcionar bien. Si se requiere un rango de entrada de más de 100 meses, la misma idea podría expandirse con un tercer CTE por años agregado antes de los meses CTE.

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
)

Answer 4

Un poco de uso creativo de CTE y productos cartesianos (combinaciones cruzadas) lo MAXRECURSION límite MAXRECURSION de 100. 3 CTE con un límite de 4 registros en el último le proporciona 40,000 registros, lo que será válido por más de 100 años. de datos. Si espera más diferencia entre @debut y @fin, puede ajustar cte3 . Además, por favor, deje de GRITAR su SQL.

-- 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

Viejo problema, pero ... Solo quería aclarar por qué OPTION (MAXRECURSION x) no está permitido en una función en línea con valores de tabla. Esto se debe a que iTVF se ingresa cuando los usas en una consulta. Y, como todos sabemos, no puede colocar esta opción en ningún otro lugar excepto al final de la consulta. Esta es LA razón por la que nunca será posible ponerlo dentro de un iTVF (a menos que el analizador sintáctico y / o el algebrizador hagan algo de magia detrás de las escenas, lo cual no creo que vaya a suceder pronto). mTVF (funciones con valores de tabla de varias declaraciones) es una historia diferente porque no se incluyen (y son tan lentas que nunca se deben usar en las consultas; no obstante, está bien usarlas en una asignación a una variable, pero entonces otra vez --- ¡cuidado con los bucles!).

Answer 6

crea una muestra simple para ti :)

/ * función de creación de bloque para la prueba en sql * / / * FUNCIÓN [fn_CTE_withLevel] (@max_level int) DEVOLUCIONES TABLA COMO DEVOLUCIÓN
(* /

/ ******************* declare la tabla simplemente reemplace la tabla real ***** /

declare @tbl table(pid varchar(15),id varchar(15))

/* use function argument */
declare @max_level int = 3

Insert Into @tbl(pid , id)
   values 

     /*lev1*/   (''0'',''1'') ,
         /*lev2*/   (''1'',''101'') ,
         /*lev2*/   (''1'',''102'') ,
     /*lev1*/   (''0'',''2'') ,
         /*lev2*/   (''2'',''201'') ,
                 /*lev3*/   (''201'',''20101'') ,
                 /*lev3*/   (''201'',''20102'') ,
         /*lev2*/   (''2'',''202'') ,
     /*lev1*/   (''0'',''3'') ,
         /*lev2*/   (''3'',''301'') ,
         /*lev2*/   (''3'',''302'') ,
     /*lev1*/   (''0'',''4'') ,
        /*lev2*/    (''4'',''401''),
        /*lev2*/    (''4'',''402'');

/ ******************* declare la tabla simplemente reemplace la tabla real ***** /

  With cte_result(pid , id , lev)
        As(
            Select pid , id , 1 as lev From @tbl t
              Where pid = ''0''  /* change to another values from list to test sub items */

              Union All

            Select t.pid , t.id , cte.lev + 1 as lev
                 From  cte_result cte
                        inner Join  @tbl t
                  On  cte.id = t.pid 
                   Where cte.lev < @max_level  -- :) this is my idea
          )

         Select * From cte_result 
             --OPTION (MAXRECURSION 100)

- descomentar para crear la función / ) /