Conversion of CTE to temp table in SQL to get all possible parents

Question

0.00/5 (No votes)

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 10-Sep-19 3:15am

Member 7673567

Updated 10-Sep-19 3:34am

Add a Solution

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

Add a Solution

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

Maciej Los · Answer 1 · 2019-09-10T03:35:00

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.

SQL

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[^]

Conversion of CTE to temp table in SQL to get all possible parents

1 solution

Solution 1

Add your solution here

Preview 0

Conversion of CTE to temp table in SQL to get all possible parents

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us