title: Recursive Loop in SQL Server
body: <blockquote>
    <p>I have a table called WarehouseStore.dws6.BeScheduleItem.
    Below is a screenshot of data found in WarehouseStore.dws6.BeScheduleItem.
    <a href="https://i.stack.imgur.com/39dlj.png" rel="nofollow">WarehouseStore.dws6.BeScheduleItem</a></p>
    
    <p>My requirement is to find missing node from WarehouseStore.dws6.BeScheduleItem Table based on below condition.</p>
  </blockquote>
  
  <ol>
  <li><p>Set all isMissing to 0</p></li>
  <li><p>If child field has IcpUid field  NOT  NULL and 
  parent has IcpUid field  NULL 
  then set  child  parent  IsMissing to 1.</p></li>
  <li><p>If child field has IcpUid field NULL and 
  parent has IcpUid field NULL  AND  parent  child  has IsMissing equal 1 
  then set  child  parent  IsMissing to 1.</p></li>
  <li><p>Repeat step 3 until there is nothing more to update.</p></li>
  </ol>
  
  <blockquote>
    <p>I was able to meet step 1 to 3 in my SQL code but not step 4 as I need to do a for loop to check if there is no more update.
    Below are my SQL code</p>
  </blockquote>
  
  <pre><code>DECLARE @TEMP TABLE (
  Code NVARCHAR(MAX) NOT NULL,
  Name NVARCHAR(MAX) NULL,
  Level INT NULL,
  IsMissing BIT NULL,
  ParentCode NVARCHAR(MAX) NULL,
  IcpUid NVARCHAR(MAX) NULL
  );
  
  INSERT INTO @TEMP(
  Code,
  Name,
  Level,
  IsMissing ,
  ParentCode ,
  IcpUid 
  )
  SELECT 
  Code,
  Name,
  Level,
  IsMissing = 0,
  ParentCode, 
  IcpUid
  FROM WarehouseStore.dws6.BeScheduleItem 
  WHERE IsDeleted = 0
  ;
  
  UPDATE P SET IsMissing = 1
  FROM @TEMP C
  INNER JOIN @TEMP P ON P.Code = C.ParentCode
  WHERE C.IcpUid IS NOT NULL 
  AND P.IcpUid IS NULL
  ;
  SELECT 
  P.Code,
  P.Name,
  P.Level,
  C.IsMissing ,
  C.ParentCode ,
  P.IcpUid 
  FROM @TEMP P
  INNER JOIN @TEMP C ON P.Code = C.ParentCode
  WHERE C.IsMissing = 1
  AND C.IcpUid IS NULL 
  AND P.IcpUid IS NULL
  ORDER BY LEVEL
  </code></pre>
  
  <blockquote>
    <p>Can you please suggest an idea how I can build a recursive loop from above code.
    Please note : there is a need to looping step 3, it might not be needed with current data but we are coding for all cases not just current data</p>
  </blockquote>