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