SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

Yesterday I wrote about SQL SERVER – SQL SERVER – Simple Example of Recursive CTE. I right away received email from regular reader John Mildred that if I can prevent infinite recursion of CTE.

Sure! recursion can be limited. Use the option of MAXRECURSION.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)
GO

Now if your CTE goes beyond 5th recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.

For example if you change MAXRECURSION to value 3. It will throw following error.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

In summary MAXRECUSION is good way to protect your CTE to go into infinite loop.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

30 thoughts on “SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

  1. Thanks for this and your previous examples regarding recursion on SQL server. I was fond of oracle’s connect by, and sad to see that it didn’t exist in SQL Server. This did the trick, though! Thanks again!

  2. It’s easy to parse delimited string with WITH statement

    DECLARE @txt as nvarchar(100)
    SET @txt = ‘Banana;Chocolote;Milk;Bread’

    ;WITH ParseText( Ltxt, Rtxt) AS (

    SELECT –anchor member
    Ltxt = LEFT (@txt, CHARINDEX( ‘;’, @txt) )
    ,Rtxt = RIGHT(@txt, LEN(@txt) – CHARINDEX( ‘;’,@txt) )

    UNION ALL –Recursive member
    SELECT

    Ltxt = LEFT(tn.Rtxt, CHARINDEX( ‘;’,tn.Rtxt) )
    ,Rtxt = RIGHT(tn.Rtxt, LEN(tn.Rtxt) – CHARINDEX( ‘;’,tn.Rtxt) )
    FROM ParseText tn where Ltxt ” — Recursive loop
    )
    SELECT
    Ltxt = CASE WHEN Ltxt = ” THEN RTxt ELSE Ltxt END
    FROM ParseText

    • I tried this example, but it didn’t go into the recursive statements. I modified it to parse the entire string and return each element of the string.

      DECLARE @txt as nvarchar(100)
      SET @txt = ‘Banana;Chocolate;Milk;Bread;';

      WITH ParseText( Ltxt, Rtxt) AS (

      SELECT
      Ltxt = LEFT (@txt, CHARINDEX( ‘;’, @txt)-1 )
      ,Rtxt = RIGHT(@txt, LEN(@txt) – CHARINDEX( ‘;’,@txt) )

      UNION ALL
      SELECT
      Ltxt = LEFT(tn.Rtxt, CHARINDEX( ‘;’,tn.Rtxt)-1 )
      ,Rtxt = RIGHT(tn.Rtxt, LEN(tn.Rtxt) – CHARINDEX( ‘;’,tn.Rtxt) )
      FROM ParseText tn
      where Rtxt ”
      )
      SELECT
      Ltxt = CASE WHEN Ltxt = ” THEN RTxt ELSE Ltxt END
      FROM ParseText

      • Btw, you need to replace the slanted single quotes to match the straight single quotes if you try to copy and paste the query into SSMS.

  3. Hi Pinal,

    Thanks for the post on CTE. Still I am not clear how can we restrict the recursion in CTE. Ex. The normal select statement (without MAXRECURSION) returns 10 records. If we give OPTION (MAXRECURSION 3), the query will throw error. If we give MAXRECURSION 4 or above it gives all the 10 records which is same as query without MAXRECURSION. Could you please clarify how can it restrict the recursion to say 3?

  4. Hi Pinal,

    I am student learning SQL server. I actaully didnot know what exactly maxrecursion function do?

    can you please be precise.

    Thanks&Regards,

    Raghuveer.

  5. @Raghuveer

    A recursive CTE can potentially go on forever. So, SQL Server limits it to 100 cycles. MAXRECURSION allows you to change that limit between 1 and 32767 or 0 which means unlimited.

  6. Hi there!

    I need help with recursive operation

    I do have 2 tables

    #1 Inspections
    ID
    PARENT_ID
    POSITION

    #2 Resources
    RESOURCES_ID
    CULTURE_LANGUAGE
    CAPTION

    DATA SAMPLE (OUTPUT)

    SELECT a.id, a.parent_id, a.position, b.culture_language, b.caption
    FROM Inspections AS a
    INNER JOIN InspectionsResources AS b ON
    a.id = b.resource_id where culture_language=’en’

    VEHICLES ID = 1, PARENT_ID = 0, POSITION=1
    FORKLIFTS ID = 2, PARENT_ID = 0, POSITION=2
    CARS ID = 3, PARENT_ID = 1, POSITION=1
    MOTORCYCLES ID = 4, PARENT_ID = 1, POSITION=2
    DOORS ID = 5, PARENT_ID = 3, POSITION=1
    HANDLES ID = 6, PARENT_ID = 3, POSITION=2

    I need to load all data for CARS only!!

    Thank you Pinal

    Alain

  7. Hi Pinal,

    How can we avoid the error
    “Msg 530, Level 16, State 1, Line 1
    The statement terminated. The maximum recursion 3 has been exhausted before statement completion. ”

    if cte goes above 100 levels, then what need to do?

  8. Hi Paresh,

    Set the MAXRECURSION hint of CTE query to any nonnegative value between 0 and 32767 as below:

    SELECT EmployeeID, ManagerID, Title
    FROM cteEmployee
    OPTION (MAXRECURSION 200);

    When 0 is specified, no limit is applied. If this option is not specified, the default limit is 100.

    Regards,
    Pinal Dave

  9. Hello Pinal Dave,

    I have a problem with MAXRECURSION hint of CTE query
    below
    declare @id int
    set @id=1;

    with cte(member_id,lft,rgt) as (
    select member_id,0,0
    from [MultilevelChain].[dbo].[member_info]
    where member_id=@id

    union all

    select t.member_id,1-t.position, t.position
    from [MultilevelChain].[dbo].[member_info] t
    inner join cte c on c.member_id = t.sponser_id)
    select sum(lft) as LeftNode,
    sum(rgt) as RightNode

    from cte
    option (maxrecursion 32767)

    it gives error message like
    Msg 530, Level 16, State 1, Line 5
    The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

    Here all below levels are searched but problem with top levelit has attach with 6 node under it. all levels are ok but not show id as 1 pls help me. i waitig to ur reply

    Tanks regards
    Niranjan Singh Pune

  10. I’m creating a .net cms application. I want a user to be able to build a menu by select multiple parent menu items. Each menu item can have two levels of inheritance.

    Using cte I can get the tree structure for the menu, however I want to be able to insert the tree structure with positioning. As users with also be able to change the positioning of the menu items.

    As under the news sub menu World news should be before UK news.

    Any help and guidance will be greatly appreciated.

    Is this even possible using CTE?

    DECLARE @menu TABLE
    (
    id int, parentId int, title varchar(128)
    )

    INSERT @menu
    SELECT 1,NULL,’Home Page’ UNION ALL
    SELECT 2,1,’News’ UNION ALL
    SELECT 3,2,’World’ UNION ALL
    SELECT 4,2,’UK’ UNION ALL
    SELECT 5,2,’Business’ UNION ALL
    SELECT 6,1,’Sports’ UNION ALL
    SELECT 7,6, ‘Football’ UNION ALL
    SELECT 8,6,’Tennis’ UNION ALL
    SELECT 9,6, ‘Formula One’ UNION ALL
    SELECT 10,6, ‘Boxing’ UNION ALL
    SELECT 11,2, ‘Politics’ UNION ALL
    SELECT 12,6, ‘Cricket’

    ;WITH MenuTree AS
    (
    SELECT
    M.id,
    M.parentId,
    M.title,
    0 AS menuLevel,
    Cast(M.title + ‘\\’ AS varchar(max)) AS treePath,
    null AS position
    FROM
    @menu AS M
    WHERE
    M.id = 1

    UNION ALL

    SELECT
    M.id,
    M.parentId,
    M.title,
    menuLevel + 1,
    Cast(MenuTree.treePath + M.title + ‘\\’ AS varchar(max)) AS treePath,
    null AS position
    FROM @menu AS M
    INNER JOIN MenuTree ON M.ParentID = MenuTree.id
    )

    SELECT * FROM menuTree order by treePath

    how do i get the positioning…

    id – title – position
    1 Home Page – 1
    2 News – 1
    5 Business – 2
    11 Politics – 3
    4 UK – 4
    3 World – 5
    6 Sports – 2
    10 Boxing – 1
    12 Cricket – 2
    7 Football – 3
    9 Formula One – 4
    8 Tennis -5

    Thanks for your time.

  11. I was hoping to restrict the recursion without throwing an error. Is there a way to get it to just stop at lets say 5 levels deep without throwing an error?

    • Well, of course. Just keep a counter which tells how deep in the recursion you are and stop there. For example like the following (which is taken and modified from example in my blog).

      Take notice that ‘lvl’ field there which tells how deep we are. In recursion part I check that lvl < 2 and recursion stops there if it ever goes so deep.

      WITH
      cte(emp_id, emp_name, sup_id, sup_name, lvl) AS
      (
      SELECT e.emp_id, e.emp_name, s.emp_id, s.emp_name, 1
      FROM t_employee e
      LEFT OUTER JOIN t_employee s ON s.emp_id = e.supervisor_id
      ),
      cte_start(emp_id, emp_name, sup_id, sup_name, lvl) AS
      (
      SELECT e.emp_id, e.emp_name, s.emp_id, s.emp_name, 1
      FROM t_employee e
      JOIN t_employee s ON s.emp_id = e.supervisor_id
      LEFT OUTER JOIN t_employee sub ON sub.supervisor_id = e.emp_id
      WHERE sub.emp_id IS NULL
      ),
      cte_recur(emp_id, emp_name, sup_id, sup_name, lvl) AS
      (
      SELECT emp_id, emp_name, sup_id, sup_name, lvl
      FROM cte_start
      UNION ALL
      SELECT c.emp_id, c.emp_name, c.sup_id, c.sup_name, r.lvl + 1
      FROM cte c
      JOIN cte_recur r ON r.sup_id = c.emp_id
      where r.lvl < 2
      )
      SELECT DISTINCT emp_name 'Employee name', sup_name 'Supervisor name', lvl
      FROM cte_recur

  12. Hi there!

    I need help with recursive operation like

    Parents 1
    ———Chield1
    ———Chield2
    ——Sub -Chield1
    ——Sub -Chield2
    Parents 2
    Parents3
    ———Chield1
    ———Chield2

    I want to display data exactly like above example with ‘-‘ operator.
    Please provide an query.
    thanks & regards
    Brijesh Shah

  13. Pingback: SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

  14. Hi pinaldave
    It’s great article and very helpful.
    I’ve question.
    I’ve sub group and trying to get all the parents so I tried this:
    ==================================
    WITH Groups_CTE AS (
    SELECT SubG.ParentID,SubG.Name FROM [Groups] AS SubG WHERE SubG.ID = 1
    UNION ALL
    SELECT ParentG.ID,ParentG.Name FROM Groups AS ParentG INNER JOIN Groups_CTE ecte ON ecte.ParentID = ParentG.ID
    )

    SELECT * FROM Groups_CTE
    ==================================
    But it didn’t work, it thow exception says:
    “The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

    What should I do to make it work properly ?

    Thanx

  15. Pingback: SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

  16. Pingback: SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

  17. It mentioned that “Now if your CTE goes beyond 5th recursion it will throw an error and stop executing.” can you please elaborate a bit as I have made max recursion 200 but didn’t get any error..thnx

  18. @Prakash,

    there is mention “OPTION (MAXRECURSION 5)” so it will throw error beyond 5th recursion, while you have made max recursion to 200, so it will throw error after 200th recursion..

  19. I have a CTE Table Category and Sub Category With ID,Parent ID relation.

    I Wana to retrieve all Nested Sub Categories In Nested Ordered Manner.

    Some one Help me soon The Result Shuld be in Following Manners.

    Main Cat

    — Sub A of Main

    —-Child Sub A of Sub A of Main

    —-Child Sub B of Sub A of Main

    —-Child Sub C of Sub A of Main

    — Sub B of Main

    —-Child Sub A of Sub B of Main

    —-Child Sub B of Sub B of Main

    —-Child Sub C of Sub B of Main

    — Sub C of Main

    —-Child Sub A of Sub C of Main

    —-Child Sub B of Sub C of Main

    —-Child Sub C of Sub C of Main

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s