Click here to Skip to main content
14,298,693 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have one user table in which i maintain parent child relationship and I want to generate the result with all user id along with its parentid and all possible Hierarchical parents as coma separated strings, my table structure is as follows.

CREATE TABLE [hybarmoney].[Users](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,   
    [USERID] [nvarchar](100) NULL,
    [REFERENCEID] [bigint] NULL 
)


and I am getting the result using the below CTE

;WITH Hierarchy (
    ChildId
    ,ChildName
    ,ParentId
    ,Parents
    )
AS (
    SELECT Id
        ,USERID
        ,REFERENCEID
        ,CAST('' AS VARCHAR(MAX))
    FROM hybarmoney.Users AS FirtGeneration
    WHERE REFERENCEID = 0

    UNION ALL

    SELECT NextGeneration.ID
        ,NextGeneration.UserID
        ,Parent.ChildId
        ,CAST(CASE 
                WHEN Parent.Parents = ''
                    THEN (CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                ELSE (Parent.Parents + ',' + CAST(NextGeneration.REFERENCEID AS VARCHAR(MAX)))
                END AS VARCHAR(MAX))
    FROM hybarmoney.Users AS NextGeneration
    INNER JOIN Hierarchy AS Parent ON NextGeneration.REFERENCEID = Parent.ChildId
    )
SELECT *
FROM Hierarchy
ORDER BY ChildId
OPTION (MAXRECURSION 0)


But I have the limitation of MAXRECURSION and when I googled, I got to know that temp tables are an alternative solution but I was not able to do the same and also i don't want to get all possible top parents, for my purpose I want to find 15 levels of hierarchical parents for each users. Is it possible to use temp tables for my purpose if possible how.

What I have tried:

I have tried only using CTE as above mentioned
Posted
Updated 10-Sep-19 3:34am
Comments
Maciej Los 10-Sep-19 9:19am
   
You can drop result of CTE to temp table using SELECT INTO statement.
What kind of issue do you have? "I have the limitation of MAXRECURSION" is not descriptive at all.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Please, read my comment to the question first.

I believe there's something wrong with your CTE, because when you set MAXRECURSION option to 0 (zero), it enable to create inifinite loop. Maximimum value for MAXRECURSION is 32767.

Below CTE is properly executed, even if it repeats almost 40000 times.
WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
    WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 0)


For further details, please see: MAXRECURSION Sql Server | SqlHints.com[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100