Recursive CTE result is infinite

Question

I have a table "ActionItem" in SQL with ActionItemId, ItemName and ParentActionItemId fields. I am creating a stored procedure which will accept ActionItemId (Id) and display all ActionItem Records that are the children of Id recursively. For this i have used CTE.

ALTER PROCEDURE [dbo].[SSP_ActionItem] 
@ActionItemId int
AS
BEGIN   
WITH ActionItemList AS
(
    --  Anchor
    SELECT ActionItem.ActionItemId,
           ActionItem.ItemName,
           ActionItem.ParentActionItemId 
    FROM ActionItem 
    WHERE ActionItemId=@ActionItemId

    UNION ALL

    --  Recursive query
    SELECT AIL.ActionItemId,
           AIL.ItemName,
           AIL.ParentActionItemId  
    FROM   ActionItem AS AI INNER JOIN  ActionItemList AS AIL 
    ON AI.ParentActionItemId=AIL.ActionItemId

)
SELECT * FROM ActionItemList
--option (maxrecursion 0)
END

Table Structure:

   SELECT TOP 1000 [ActionItemId]
    ,[ParentActionItemId]
    ,[ItemName]
    FROM [ActionItem]

This sp returns infinite records.I dont understand where i am going wrong. can anyone help? I am new to this CTE concept.

It might help if you could share the table structure and sample data — shree.pat18, Aug 25 '14 at 06:23
Your recursive part does not have any column from the `ActionItem` table and is basically repeating all information from the anchor. It will be infinite by design. @JasonGoemaat: like your question :) — cha, Aug 25 '14 at 06:25
"ON AI.ParentActionItemId=AIL.ActionItemId" this is the recursive part and it has the column ParentActionItemId from ActionItem — user3286962, Aug 25 '14 at 06:30
@cha:, The problem is solved.... But am still wondering why i have to include columns of ActionItem in recursive query section of CTE? Because the way i see it logically, it hardly matters if i use the table or CTE result as they all point to the same Column.... Which concept am in not aware of here? — user3286962, Aug 26 '14 at 04:07
I think it will come to you eventually. Try practising recursive CTE from simple samples and then you will figure it out — cha, Aug 26 '14 at 04:09

score 2 · Accepted Answer · answered Aug 25 '14 at 08:26

2

Procedure should be:

ALTER PROCEDURE [dbo].[SSP_ActionItem] 
@ActionItemId int
AS
BEGIN   
WITH ActionItemList AS
(
    --  Anchor
    SELECT ActionItem.ActionItemId,
           ActionItem.ItemName,
           ActionItem.ParentActionItemId 
    FROM ActionItem 
    WHERE ActionItemId=@ActionItemId

    UNION ALL

    --  Recursive query
    SELECT AI.ActionItemId,
           AI.ItemName,
           AI.ParentActionItemId  
    FROM   ActionItem AS AI INNER JOIN  ActionItemList AS AIL 
    ON AI.ParentActionItemId=AIL.ActionItemId

)

answered Aug 25 '14 at 08:26

The problem is solved.... But am still wondering why i have to include columns of ActionItem in recursive query section of CTE? Because the way i see it logically, it hardly matters if i use the table or CTE result as they all point to the same Column.... Which concept am in not aware of here? – user3286962 Aug 26 '14 at 04:08
Earlier you were including CTE result's columns in recursive query, that ends in infinite loops. For ex. see data from this question http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example If you include, CTE's result it will select "Ken" again and again, It will not select "Terri" or "Roberto" – Aug 26 '14 at 04:28

Recursive CTE result is infinite

1 Answers1