Get more than 1 result set for recursive CTE?
common-table-expression, sql-server
Solution
To present multiple result sets to your client, you're going to have to use a cursor or a while loop to perform independent `SELECT` operations. You can't do that from a CTE, since a CTE can only be used by exactly one subsequent query.
Now, the source of the problem has nothing to do with cursors really, but the fact that you're using an HTML repeater. Why do you need to use an HTML repeater for this? A simple DataReader can loop through all of the results from the CTE's single set, and make conditional formatting decisions based on the loop and determining when the root ID changes. So I suggest you look into solving the presentation problem a different way, rather than trying to coerce SQL Server to accommodate your presentation implementation.
Problem
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)