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

Michael Harmon · Accepted Answer · 2013-04-22 02:42:30Z

5

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.

answered Apr 22, 2013 at 2:42

Michael Harmon

7465 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naupad Doshi Over a year ago

Thanks Michael Harmon..Thanks for the code. It works perfect. Thanks again for the help.

Dinesh Vaitage · Accepted Answer · 2016-02-26 10:00:34Z

0

Try this simple example

CREATE PROCEDURE proc (
    IN i INTEGER,
    IN j INTEGER)
BEGIN
    DECLARE z integer;
    set z=i;
   while (z<j) do
     begin
       set z=z+1;
     end;
  end while;
END;

answered Feb 26, 2016 at 10:00

Dinesh Vaitage

3,2031 gold badge22 silver badges17 bronze badges

Collectives™ on Stack Overflow

Sql Stored Procedure while loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related