SQL recursive using CTE

Asked

Viewed 604 times

-4

I am mounting an SQL statement using CTE (common table expression) and recursiveness, but I’m getting the following error:

Message 530, Level 16, Status 1, Line 5 Instruction terminated. A 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’s an example I put together that reproduces the behavior of my consultation

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 previous with a recursive query following the same logic of the example that I added.

sql sql-server
  • 1

    @Jeffersonquesado actually goes from month to month 01/09/2019 and it would be by my calculations some 140 months that should be returned by the consultation

  • 2

    Yes, then I noticed that I had read the DATEADD erroneously, it is so much so that I removed my previous comment. By the way, making a recursion of 100+ levels in SQL is usually a bad Smell. This seems to me something much more imperative than declarative, so I particularly believe that this solution is using screwdrivers to fix a nail.

  • 2

    @Jeffersonquesado is exactly what you said uma chave de fenda para fixar um prego, but there is already a precedent with this recursive select, I am not creating this monster from scratch, so for now I will only arrange the necessary to be able to run the trial and solve a point problem I have. You must have already worked on legacy systems and you should know what it’s like to handle these magical procedures created by other devs.

  • If you explain the exact result you want, along with the source data, we can tell you a solution that really meets your needs, attacks the database less and is easy to understand

  • @Sorack the whole point is that today there is already a trial full of business rules within it, I have put together just one example with the logic of the problem I was having inside this trial, today I do not feel comfortable in handling this trial, because I do not know the impact for the whole system, but certainly tetaria to the maximum avoid this recursiveness, but as the business is already done, I will only correct what I need.

1 answer

2


In the use of CTE needs to have certain care, especially in the possibility of entering loop infinite.

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)

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.