Get more than 1 result set for recursive CTE?

Question

I have a simple table which has leafs and sub leafs info. ( like a forum questions)

A main message is defined where childId and ParentID are the same

So here we see 2 main questions and their answers.

I've also managed to calc the depth of each element :

In short this is the main query :

    WITH CTE AS
    (
       SELECT childID
             ,parentID,
             0 AS depth,name
       FROM   @myTable
       WHERE   childID = parentID AND childID=1 -- problem line
       
       UNION ALL
       
       SELECT TBL.childID
             ,TBL.parentID,
              CTE.depth + 1 , TBL.name 
       FROM   @myTable AS TBL
               INNER JOIN CTE  ON  TBL.parentID = CTE.childID
       WHERE   TBL.childID<>TBL.parentID
    )
    SELECT childID,parentID,REPLICATE('----', depth) + name        

But the problem is Line #8 (commented).

I currently ask "give me all the cluster for question id #1"

So where is the problem ?

I want to have multiple result set , for each question !

so here i need to have 2 result sets :

one for childId=parentId=1 and one for one for childId=parentId=6

full working sql online

(and I dont want to use cursor)

Answer 1

You can build your queries dynamically.

DECLARE @SQL NVARCHAR(MAX) =
(SELECT '
WITH CTE AS
(
   SELECT childID
         ,parentID,
         0 AS depth,name
   FROM   myTable
   WHERE   childID = parentID AND childID = '+CAST(childID AS NVARCHAR(10))+'

   UNION ALL

   SELECT TBL.childID
         ,TBL.parentID,
          CTE.depth + 1 , TBL.name 
   FROM   myTable AS TBL
           INNER JOIN CTE  ON  TBL.parentID = CTE.childID
   WHERE   TBL.childID<>TBL.parentID
)
SELECT childID,parentID,REPLICATE(''----'', depth) + name
FROM   CTE   
ORDER BY
      childID;'

FROM  myTable
WHERE childID = parentID
FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)');

EXEC sp_executesql @SQL;

Update:

As suggested by Bogdan Sahlean we can minimize compilations by making the actual query parameterized.

DECLARE @SQL1 NVARCHAR(MAX) =
'WITH CTE AS
(
   SELECT childID
         ,parentID,
         0 AS depth,name
   FROM   myTable
   WHERE   childID = parentID AND childID = @childID

   UNION ALL

   SELECT TBL.childID
         ,TBL.parentID,
          CTE.depth + 1 , TBL.name 
   FROM   myTable AS TBL
           INNER JOIN CTE  ON  TBL.parentID = CTE.childID
   WHERE   TBL.childID<>TBL.parentID
)
SELECT childID,parentID,REPLICATE(''----'', depth) + name
FROM   CTE   
ORDER BY
      childID;'

DECLARE @SQL2 NVARCHAR(MAX) =
(SELECT 'exec sp_executesql @SQL, N''@childID int'', '+CAST(childID AS NVARCHAR(10))+';'
FROM  myTable
WHERE childID = parentID
FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)');

EXEC sp_executesql @SQL2, N'@SQL NVARCHAR(MAX)', @SQL1;