CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012sql-server-2008-r2
Ram
Ram

Reputation: 91

Rewrite recursive CTE using while loop

I want to rewrite the Recursive CTE to while loop in SQL Server.

How can I rewrite? Sample Query is in the below.

WITH CTE
 AS (
 SELECT DISTINCT
        ID,
        [NAME],
        ID AS PARENTID,
        ITEMTYPE,
        COST
 FROM TABLE1
 UNION ALL
 SELECT T.ID,
        C.[NAME],
        C.ID AS PARENTID,
        T.ITEMTYPE,
        T.COST
 FROM CTE AS C
      INNER JOIN TABLE1 T ON C.ID = T.ID
 WHERE C.ITEMTYPE <> 'individual')
 SELECT *
 FROM CTE;

Upvotes: 1

Views: 5213

Answers (2)

Pawan Kumar
Pawan Kumar

Reputation: 2021

You need something like this. Not very sure why you need the loop though.

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL
        DROP TABLE #temp
    
    
    IF OBJECT_ID('tempdb..#tempFinal') IS NOT NULL
        DROP TABLE #tempFinal
    
     SELECT DISTINCT
            ID,
            [NAME],
            ID AS PARENTID,
            ITEMTYPE,
            COST
     INTO #temp
     FROM TABLE1
    
    DECLARE @ID as INT 
    
    WHILE EXISTS ( SELECT TOP 1 1 FROM #temp )
    BEGIN
        
        SELECT TOP 1 @ID = ID FROM #temp 
    
        SELECT T.ID,
            C.[NAME],
            C.ID AS PARENTID,
            T.ITEMTYPE,
            T.COST
         INTO #tempFinal
         FROM #temp AS C
              INNER JOIN TABLE1 T ON C.ID = T.ID
         WHERE C.ITEMTYPE <> 'individual' AND T.ID = @ID
    
         DELETE FROM #temp WHERE ID = @ID
    
    END

SELECT * FROM #tempFinal

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453648

The rewrite is quite simple.

DECLARE @LoopCount INT = 0;

 SELECT DISTINCT
        ID,
        [NAME],
        ID AS PARENTID,
        ITEMTYPE,
        COST
 INTO #CTE
 FROM TABLE1

 WHILE @@ROWCOUNT > 0 AND @LoopCount < 100
 BEGIN
 INSERT INTO #CTE
  SELECT T.ID,
        C.[NAME],
        C.ID AS PARENTID,
        T.ITEMTYPE,
        T.COST
 FROM #CTE AS C
      INNER JOIN TABLE1 T ON C.ID = T.ID
 WHERE C.ITEMTYPE <> 'individual';

 SET @LoopCount += 1;

 END;

 IF @LoopCount = 100
    THROW 50000, 'Max recursion limit exceeded', 0

 SELECT *
 FROM #CTE

Upvotes: 2

Related Questions