title: Using SQL Server, how to return a list from a While loop, and should I be using a While loop?
body: <p>I have table A, with columns <code>Id</code> and <code>ParentId</code>. Given an <code>Id</code>, I want to return all <code>ParentId</code>. </p>
  
  <p>Meaning, given an <code>Id</code>, I want to return all entries' <code>ParentId</code>, and then I want to check to see if the recorded <code>ParentId</code> is entered into the table as an Id, if so I want to find it's <code>ParentId</code>, etc...</p>
  
  <p>For example, if I have the simple example of a table like:</p>
  
  <pre><code>Id | ParentID
  ---+---------
  1    0    
  2    1
  3    2
  4    3
  5    4 
  6    5
  7    6
  </code></pre>
  
  <p>My query to print all the parents of <code>Id=7</code> looks like this:</p>
  
  <pre><code>DECLARE @ParentId INT;
  
  SET @ParentId = 7;
  
  WHILE(@ParentId &gt; 0)
  BEGIN 
    SELECT @ParentId = ParentId
    FROM A
    WHERE Id = @ParentID
  
    PRINT @ParentId
  END
  </code></pre>
  
  <p>This query prints all the <code>ParentId</code>. But, is there a way to return the IDs? In that, if I was calling this query from PHP I would want to get a list of the ParentIds, but my query doesn't actually return anything.</p>
  
  <p>Additionally, I wonder if a while loop is the correct solution. Would a recursive query be faster? If so, any advice on how to convert this to a recursive query?</p>
  
  <p>*Edit </p>
  
  <p>I need to access the list of ParentIds from PHP. Now I'm trying to build a comma-delimited list of Ids to return from the query, which I can later parse in PHP. However, the below query gives me error: </p>
  
  <blockquote>
    <p>A RETURN statement with a return value cannot be used in this context.</p>
  </blockquote>
  
  <pre><code>DECLARE @ParentId INT, @ParentList varchar(max);
  
      SET @ParentId = 7;
  
      WHILE(@ParentId &gt; 0)
      BEGIN 
        SELECT @ParentId = ParentId
        FROM A
        WHERE Id = @ParentID
  
        SET @ParentList = @ParentList | CAST(@ParentId as varchar(20)) | ','; 
      END
      RETURN @ParentList;
  </code></pre>
  
  <p>What should I do to return a list? Additionally, how can I get the returned value when I execute this statement in PHP?</p>