title: Stored Procedure using Recursive Query
body: <p>I am writing a stored Procedure which should return me result and the same result will be passed as input to the stored Procedure and the recursion should stop after the input and output are same at any particular time.</p>
  
  <p>Below is the stored Procedure/function and the Run Command (New to Stored Procedure).</p>
  
  <pre><code>CREATE FUNCTION dbo.Ancestors (@List VARCHAR(MAX))
  RETURNS TABLE
  AS
  RETURN
  WITH CTE AS
  (
   SELECT DISTINCT Child AS RESULT FROM example WHERE Parent IN( SELECT Id = 
   Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM 
   dbo.SplitInts(@List, ','))
   UNION ALL
   SELECT DISTINCT Parent AS RESULT FROM example WHERE Parent IN( SELECT Id = 
   Item FROM dbo.SplitInts(@List, ',')) or child IN (SELECT Id = Item FROM 
   dbo.SplitInts(@List, ','))
   )
  SELECT RESULT FROM CTE
  UNION
  SELECT RESULT FROM CTE
  GO
  
  CREATE PROCEDURE GetAncestors (@thingID VARCHAR(MAX))
  AS
  SELECT RESULT FROM dbo.Ancestors(@thingID)
  GO
  
  EXEC GetAncestors @thingID = '100'    
  </code></pre>
  
  <blockquote>
    <p>Result is - '100,101' </p>
  </blockquote>
  
  <pre><code>EXEC GetAncestors @thingID = '100,101'
  </code></pre>
  
  <blockquote>
    <p>Result is - '100,101,102'</p>
  </blockquote>
  
  <pre><code>EXEC GetAncestors @thingID = '100,101,102'
  </code></pre>
  
  <blockquote>
    <p>Result is - '100,101,102'</p>
  </blockquote>
  
  <p>What i am actually looking for it is to give the result at one shot by passing 100 or 101 or 102 </p>
  
  <blockquote>
    <p>Result should be - '100,101,102'</p>
  </blockquote>
  
  <p>Below is the Example Table:</p>
  
  <pre><code>|     Parent          |     Child        |
  |---------------------|------------------|
  |      100            |      101         |
  |---------------------|------------------|
  |      101            |      102         |
  |---------------------|------------------|
  </code></pre>