Snowflake SQL中While循环执行报错求助：遍历员工存入临时表

直接语法错误原因 #

报错里的<是HTML转义字符，实际需用<；同时Snowflake的WHILE循环语法不需要给条件加括号，这是触发语法错误的直接原因。除此之外，代码还有多处语法和逻辑问题：

代码中的其他问题 #

• 变量赋值错误：Snowflake中会话变量赋值需用set 变量名 = 值;，而非select 变量名 = ...的写法；循环中使用的i未定义，应改用已声明的counter。
• 语句缺少分号：每个SQL语句结尾必须加;分隔，否则会触发语法错误。
• 逻辑冗余：逐行遍历员工的方式效率极低，递归CTE可一次性获取所有层级的下属关系，完全不需要循环。

修正后的循环版本（仅解决语法问题） #

set ro = (select count(*) from tblEmployees);
execute immediate $$ 
begin
    set counter := 1;
    set iRows = $ro;
    set EmployeeID = '';
    while counter < $iRows do 
        -- 正确赋值变量
        set EmployeeID = (select TblEmployeeID from tblEmployees emp where emp.RowId = $counter);
        
        insert into tblChildren 
        with cteChildren as (
            select emp.tblEmployeeID as ChildId, emp.WorkEmail, emp.EmployeeManagerId
            from tblEmployees emp
            where emp.EmployeeManagerId = $EmployeeID        
            union all
            select emp.tblEmployeeID as ChildId, emp.WorkEmail, emp.EmployeeManagerId
            from tblEmployees emp
            join cteChildren on emp.EmployeeManagerId = cteChildren.ChildId
        )  
        select $EmployeeID as EmployeeManagerId, c.ChildId from cteChildren c;
        
        -- 递增计数器
        set counter := $counter + 1;
    end while;
end;
$$;

更优的无循环版本（推荐） #

直接用递归CTE一次性获取所有员工的下属层级并插入临时表，性能远高于循环：

insert into tblChildren
with recursive cteChildren as (
    -- 锚点成员：获取所有员工的直接下属
    select 
        emp.tblEmployeeID as EmployeeManagerId,
        sub.tblEmployeeID as ChildId
    from tblEmployees emp
    left join tblEmployees sub on emp.tblEmployeeID = sub.EmployeeManagerId
    where sub.tblEmployeeID is not null -- 过滤无下属的员工
    union all
    -- 递归成员：获取间接下属
    select 
        cte.EmployeeManagerId,
        emp.tblEmployeeID as ChildId
    from cteChildren cte
    join tblEmployees emp on cte.ChildId = emp.EmployeeManagerId
)
select EmployeeManagerId, ChildId from cteChildren;

内容的提问来源于stack exchange，提问作者user18697015