sqlsql-serverrecursioncommon-table-expression

Recursive CTE result is infinite


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 don't understand where I am going wrong.


Solution

  • 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    
    )