sql server 2008 - The maximum recursion 100 has been exhausted before statement completion error showing in SQL Query -

"the maximum recursion 100 has been exhausted before statement completion" error showing in sql query

with departmentcte (   select  id,          departmentname,          rootid,          recursionlevel = 1,          parentroot = cast('none' nvarchar(max)),         lastparentcatid = rootid,         displayorder    department union select  cte.id,          cte.departmentname,         cte.rootid,         cte.recursionlevel + 1,         parentroot = case when cte.recursionlevel = 1 '' else cte.parentroot + '>' end + c.departmentname,         lastparentcatid = c.rootid,         cte.displayorder    departmentcte cte         inner join department c             on c.id = cte.rootid  ), maxrecursion (   select  id,          departmentname,          rootid,          parentroot,          rownum = row_number() over(partition id order recursionlevel desc),         displayorder    departmentcte ) select  id, departmentname, rootid, parentroot    maxrecursion    rownum = 1; 

you can limit number of recursion levels using maxrecursion option hint this: option (maxrecursion 0); value (between 0 , 32767) specifies number of levels of recursion, 0 meaning infinite.

from documentation cte:

an incorrectly composed recursive cte may cause infinite loop. example, if recursive member query definition returns same values both parent , child columns, infinite loop created. prevent infinite loop, can limit number of recursion levels allowed particular statement using maxrecursion hint , value between 0 , 32,767 in option clause of insert, update, delete, or select statement. lets control execution of statement until resolve code problem creating loop. server-wide default 100. when 0 specified, no limit applied. 1 maxrecursion value can specified per statement. more information, see query hints (transact-sql).

and documentation query hints states:

maxrecursion number

specifies maximum number of recursions allowed query. number nonnegative integer between 0 , 32767. when 0 specified, no limit applied. if option not specified, default limit server 100.

when specified or default number maxrecursion limit reached during query execution, query ended , error returned.

because of error, effects of statement rolled back. if statement select statement, partial results or no results may returned. partial results returned may not include rows on recursion levels beyond specified maximum recursion level.

to use statement append option clause after clause in query using recursive cte.

specifying 0 might lead bad stuff if query goes infinite loop though.