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.