Snowflake带数组参数的父存储过程调用子存储过程报错及状态返回咨询

报错原因 #

• 变量名拼写错误：循环条件里用了process_name.length，但存储过程参数定义的是process_nm array，不存在process_name这个标识符，直接触发"invalid Identifier"错误。
• return语句位置错误：原代码把return放在while循环内部，第一次循环执行到该语句就会直接终止存储过程，根本没法遍历完所有数组元素。
• 缺少异常捕获机制：没处理子存储过程调用可能出现的异常，没法记录失败状态。
• 无状态收集逻辑：没定义变量存储每个process_nm的执行结果，无法最终返回汇总状态。

修正后的实现代码 #

CREATE OR REPLACE PROCEDURE Parent_Proc(process_tbl varchar, audit_tbl varchar, process_nm array)
returns variant -- 改用variant类型返回结构化状态结果
as $$
declare
    COUNTER int default 1;
    result variant default array_construct(); -- 存储每个process的执行状态
    current_process varchar;
begin
    -- 遍历数组中所有process_nm
    while (COUNTER <= array_size(process_nm)) do
        current_process := process_nm[COUNTER-1];
        begin
            -- 调用子存储过程
            call Child_Proc(process_tbl, audit_tbl, current_process);
            -- 记录成功状态
            result := array_append(result, object_construct('process_nm', current_process, 'status', 'SUCCESS'));
        exception
            when others then
                -- 捕获异常，记录失败状态及错误信息
                result := array_append(result, object_construct('process_nm', current_process, 'status', 'FAILED', 'error_msg', sqlerrm));
        end;
        COUNTER := COUNTER + 1;
    end while;
    
    -- 返回所有process的执行状态汇总
    return result;
end;
$$;

代码说明 #

• 变量名修正：把process_name.length替换为array_size(process_nm)，Snowflake中用array_size函数获取数组长度更稳妥。
• 状态收集：用result数组变量，通过array_append和object_construct存储每个process的名称、状态，失败时额外记录错误信息。
• 异常处理：在子过程调用外层加begin...exception块，捕获所有异常，保证单个子过程失败不会中断整个父过程的遍历。
• 返回逻辑：把return移到循环结束后，返回包含所有process执行状态的结构化数组，方便后续解析。
• 返回类型调整：从varchar改为variant，适配结构化的状态数据输出。

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