SQL存储过程中的while循环

Question

数据示例：

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

现在，如果我搜索名称类似于“%child-1%”，我想要以下记录：上面数据中的Row-1、Row-3、Row-4和row-5。我应该编写什么样的存储过程才能返回不同的行？

我的想法是，如果我搜索一段文本，它将继续从表中选择记录，直到parentID为null。因此，如果我对“child-1”进行了like搜索，基本的sql查询将返回Row-4和Row-5。但是我的存储过程应该循环检查Row-4是否有不为null的parentid，因此它获得了一个ID = row-4的parentid为3的行。现在它获得了一个ID = row-3的parentid为1的行，并且获得了row-1。现在row-1的parentd为NULL，所以它停止了。

我正在使用这个存储过程实现树形视图中的搜索功能，我希望在搜索后保留父子层次结构。

到目前为止，我尝试过这个，但是我对存储过程还很陌生。

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

- Naupad Doshi

Answer 1

我使用面向集合的SQL重新编写了这段代码，而不是使用过程式代码。我使用递归CTE来查找给定子级的“父级”。以下是我的存储过程：

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

我随后使用以下指令运行它：

exec find_parents @childname='child-1%'

如果我的结果正确，那么这个解决方案应该更适合处理大量的数据。我还将其编码为存储过程，正如您所指示的那样。

要查看完整代码，请参见SQL Fiddle： 查找父级 SQL Fiddle 演示 如果我的解决方案正确，请将其标记为答案。谢谢。

- Michael Harmon

---

感谢Michael Harmon，谢谢你提供的代码。它完美地运行了。再次感谢你的帮助。 - Naupad Doshi

Answer 2

试试这个简单的例子

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;

- Dinesh Vaitage