Recursive SQL using CTE

-4

I'm mounting an SQL statement using   

Message 530, Level 16, State 1, Line 5 The statement was terminated. THE   maximum recursion 100 was exhausted before the end of the instruction.

SQL Server version:

  Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)
  Dec 13 2016 05:22:44 Copyright (c) Microsoft Corporation Enterprise   Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard   6.3 (Build 14393:)

Here is an example that I put together that reproduces the behavior of my query

DROP TABLE IF EXISTS #DADOS
CREATE TABLE #DADOS (
    ID INTEGER,
    DATA DATETIME
)

INSERT INTO #DADOS VALUES(1, '01/09/2007')
INSERT INTO #DADOS VALUES(2, '01/09/2016')

GO

WITH V_DADOS
    AS( SELECT  *
        FROM    (
                  SELECT  ID, 
                        DATA,                         
                          CASE WHEN MONTH(DATA) <= MONTH(GETDATE())  THEN 
                              CAST('01/'+CAST(DATEPART(MM,DATA) AS VARCHAR)+'/'+CAST(DATEPART(YYYY,DATEADD(YEAR,1,GETDATE()))AS VARCHAR) AS DATE) 
                          ELSE
                               CAST('01/'+CAST(DATEPART(MM,DATA) AS VARCHAR)+'/'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) AS DATE) 
                          END AS DATA_MAX                          
                  FROM    #DADOS
                ) SUB
        WHERE   ID = 1
        AND     SUB.DATA < SUB.DATA_MAX
        UNION   ALL
        SELECT  ID,
                DATEADD(M,1,DATA) DATA,                
                DATA_MAX
        FROM    V_DADOS     
        WHERE   DATA < DATA_MAX     
    )

    SELECT  ID, DATA, DATA_MAX
    FROM    V_DADOS

The idea of recursion is to complete a table of plots that we have in the system, today there is already a procedure with a recursive query following the same logic of the example that I added.

    
asked by anonymous 03.10.2018 / 15:07

1 answer

2

When using CTE you need to be careful, especially in the possibility of entering loop infinity.

You can set the maximum recursion of a CTE using the following command:

-- definir o limite de recursão para 1.000
OPTION (MAXRECURSION 1000)

-- remover o limite de recursão
OPTION (MAXRECURSION 0)
    
03.10.2018 / 15:25