title: Sql Stored Procedure while loop
body: <p>Example of Data:</p>
  
  <pre><code>ID  Name       ParentID
  1   parent-1   NULL
  2   parent-2   NULL
  3   sub        1
  4   child-1-1  3
  5   child-1-2  1
  6   child-2-1  2
  </code></pre>
  
  <p><strong>Now If I search for Name like '%child-1%', I want the following records: Row-1, Row-3, Row-4 and row-5 in above data.  What kind if stored procedure can I write that returns me disctinct rows?</strong></p>
  
  <p>My Idea is If I search for a text, it will continue selecting the records from the table until the parentID is null. So If I do a like search for 'child-1', a basic sql query returns Row-4 and Row-5. But my procedure shud check in a loop that Row-4 has parentid which is not null, so it gets a row with ID= parentid of row-4 which is 3. Now it gets a row with ID = parentid of row-3 which is 1 and gets row-1. Now parentd of row-1 is NULL so it stops.</p>
  
  <p>I am using this stored procedure to implement a search functionality in tree view in which I want to keep the parent-child hierarchy after search.</p>
  
  <p>so far I have tried this but I am new to stored procedures:</p>
  
  <pre><code>USE DBname
  Go
  DECLARE @ParentID int
  Declare @myresultset Cursor
  Set @myresultset = CURSOR for Select ParentID from mytable where Name like 'child-1%'
  OPEN @myresultset 
  Fetch NEXT from @myresultset
  into @ParentID
  While @@Fetch_Status=0
  Begin
   While @ParentID is not NULL
    Begin
      Select @ParentID = ParentID from mytable where ID=@ParentID
      Select distinct * from mytable where ID=@ParentID
    End
   Fetch Next from @myresultset
   into @ParentID
  End
  close @myresultset
  </code></pre>