Sql Stored Procedure while loop

Question

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 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?

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.

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.

so far I have tried this but I am new to stored procedures:

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

Answer 1

Instead of coding this using procedural code, I recoded this using set oriented SQL. I am using a recursive CTE to find the "parents" of the given children. Here is my stored procedure:

CREATE PROC find_parents (@childname varchar(20)) as

;WITH heirarchy_cte (ID, Name, ParentID, Level) as
(SELECT e.ID, e.Name, e.ParentID, 0 as Level
    FROM mytable as e
    where e.Name like @childname
UNION ALL
SELECT e.ID, e.Name, e.ParentID, Level+1
    FROM mytable as e
    INNER JOIN heirarchy_cte as h
    ON h.ParentID=e.ID
    )
SELECT DISTINCT ID, Name, ParentID
FROM heirarchy_cte
ORDER BY ID

I then run it with:

exec find_parents @childname='child-1%'

If my results are correct, then this solution should scale better for a larger quantity of data. I also coded it as a stored procedure as you had indicated.

To see the full code, please see the SQL Fiddle at: Find Parents SQL Fiddle Demo

If my solution is correct, please mark it as the answer. Thanks.