Sql Stored Procedure while loop -

example of data:

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 

now if search name '%child-1%', want following records: row-1, row-3, row-4 , row-5 in above data. kind if stored procedure can write returns me disctinct rows?

my idea if search text, continue selecting records table until parentid null. if search 'child-1', basic sql query returns row-4 , row-5. procedure shud check in loop row-4 has parentid not null, gets row id= parentid of row-4 3. gets row id = parentid of row-3 1 , gets row-1. parentd of row-1 null stops.

i using stored procedure implement search functionality in tree view in want keep parent-child hierarchy after search.

so far have tried new stored procedures:

use dbname go declare @parentid int declare @myresultset cursor set @myresultset = cursor select parentid mytable name 'child-1%' open @myresultset  fetch next @myresultset @parentid while @@fetch_status=0 begin  while @parentid not null   begin     select @parentid = parentid mytable id=@parentid     select distinct * mytable id=@parentid   end  fetch next @myresultset  @parentid end close @myresultset 

instead of coding using procedural code, recoded using set oriented sql. using recursive cte find "parents" of given children. here stored procedure:

create proc find_parents (@childname varchar(20))  ;with heirarchy_cte (id, name, parentid, level) (select e.id, e.name, e.parentid, 0 level     mytable e     e.name @childname union select e.id, e.name, e.parentid, level+1     mytable e     inner join heirarchy_cte h     on h.parentid=e.id     ) select distinct id, name, parentid heirarchy_cte order id 

i run with:

exec find_parents @childname='child-1%' 

if results correct, solution should scale better larger quantity of data. coded stored procedure had indicated.

to see full code, please see sql fiddle at: find parents sql fiddle demo

if solution correct, please mark answer. thanks.