The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

      If you are using recursive query in CTE for Traversing a hierarchy; then there is a chance to get the mentioned error. This comes when your data has hierarchy depth greater than 100. Because by default SQL allows MAXRECURSION upto the 100th level. While the Max recusion length is 32767, so you can change this option and set your desired depth upto 32767.

Let's understand it by taking an example below :

----- Create a table -------





CREATE TABLE TestTable

(

 UserID     VARCHAR(10)

,UserName   VARCHAR(50)

,ParentID   VARCHAR(10)

)



----- Insert the records into TestTable -------





INSERT INTO TestTable VALUES
(10001,'User1',NULL)



DECLARE @LOOPCOUNT
INT, @UserID INT, @ParentID INT



SET @LOOPCOUNT
= 1

SET @UserID = 10001

SET @ParentID = NULL



WHILE (@LOOPCOUNT < 200)

   BEGIN

       INSERT INTO TestTable

       SELECT  @UserID+@LOOPCOUNT
AS UserID 

              ,'User' + CAST(@LOOPCOUNT AS VARCHAR) AS UserName

              ,@UserID+@LOOPCOUNT-1 AS ParentID

       SET @LOOPCOUNT = @LOOPCOUNT + 1

   END

Now see the records inserted in the table TestTable :

SELECT * FROM TestTable



 UserID    UserName    ParentID

 10001    User1        NULL

 10002    User1        10001

 10003    User2        10002

 10004    User3        10003

 10005    User4        10004

 …….        …….        …….

  Now if you wish to see the users mapped and down the hierarchy of UserID '10001' and executed the below query :

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

You will get the error message:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

So now, to avoid the error, give OPTION (MAXRECURSION 200) or any other range or OPTION (MAXRECURSION 0) to get max range...

;WITH CTE

AS

(

SELECT  CAST('10001' AS VARCHAR(10)) UserID

       ,CAST('User1' AS VARCHAR(50)) UserName

       ,CAST(NULL AS VARCHAR(10)) ParentID

UNION  ALL 

SELECT TT.UserID, TT.UserName, TT.ParentID

FROM    TestTable  TT

       INNER
JOIN CTE

       ON
CTE.UserID = TT.ParentID

)

SELECT * FROM CTE

OPTION (MAXRECURSION
0)

And you are done ...


Please Note that casting is done to avoid another error:

Msg 240, Level 16, State 1, Line 1. Types don't match between the anchor and the recursive part in column "a" of recursive query "CTE".   Which is caused by datatype mismatch...

Reference: Govind Badkur(http://govindbadkur.blogspot.com)