You are here:Home » tsql » "The statement terminated. The maximum recursion 100 has been exhausted before statement completion" with CTE in SQL server 2005

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion" with CTE in SQL server 2005


Problem :
When I was working with stored procedures to get the hierarchical data in SQL Server 2005, I made one mistake. Let me explain the what was that mistake. I don't want to repeat the same. I have also posted one post for the CTE and MAXRECURSION option.

I was using CTE (Common table expression) to get the hierarchical data and forgot to put MAXRECURSION option in t-SQL statement in the stored procedure. It was working very well if the hierarchy depth is less than 100, but it was creating an issue when hierarchy depth greater than 100.

An Example.
IF ( Object_id('CTEMaxLevelTab') > 0 )
  DROP TABLE CTEMaxLevelTab

GO

CREATE TABLE CTEMaxLevelTab
  (
     ChildId   BIGINT IDENTITY(1, 1)
     ,ParentId BIGINT
  )

GO

-- Inserting demo records
INSERT INTO CTEMaxLevelTab
            (ParentId)
SELECT TOP 500
  NULL
FROM   sys.sysobjects a
       CROSS JOIN sys.sysobjects b

GO

UPDATE CTEMaxLevelTab
SET  ParentId = ChildId - 1

GO
Now write the below code and execute in your query analyzer and see the what's result with the script.
;WITH cteLevels
AS
(
SELECT
  ChildId   AS Child
  ,ParentId AS Parent
  ,1        AS [Level]
FROM   CTEMaxLevelTab 
WHERE CHILDID = 1


UNION ALL

SELECT
  ChildId      AS Child
  ,ParentId    AS Parent
  ,[Level] + 1 AS [Level]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentId = Child ) 


)


SELECT
  *
FROM   cteLevels

This is the error occurred after 100 recursions reached.


Solution :
Now going to close the success and resolve the issue with Max-recursion option. Let's run the below revised script and see you in success.
;WITH cteLevels
AS
(
SELECT
  ChildId   AS Child
  ,ParentId AS Parent
  ,1        AS [Level]
FROM   CTEMaxLevelTab 
WHERE CHILDID = 1


UNION ALL

SELECT
  ChildId      AS Child
  ,ParentId    AS Parent
  ,[Level] + 1 AS [Level]
FROM   CTEMaxLevelTab
       INNER JOIN cteLevels
         ON ( ParentId = Child ) 


)


SELECT
  *
FROM   cteLevels
OPTION (MAXRECURSION 0); 

Hope you always use this option to come out of this issue.