调用含DML且返回多值的Snowflake存储过程的最优方案

调用包含INSERT等DML操作、且返回多值的Snowflake存储过程时，用TABLE()函数调用会触发编译错误：

SQL compilation error: Invalid statement type 'INSERT' for child job from stored procedure in FROM clause on TABLE 'EXAMPLE'

场景细节 #

• 带INSERT的存储过程调用失败：定义EXAMPLE_WITH_INSERT()，内部执行INSERT并返回TABLE类型，被CALLER_WITH_INSERT()用TABLE()调用时直接报错
• 去掉INSERT就正常：删掉存储过程里的INSERT语句后，TABLE()能正常调用，但满足不了业务需求
• 临时 workaround：把返回类型改成OBJECT，调用时用INTO接收结果能跑，但这不是最优解

另外单独调用EXAMPLE_WITH_INSERT()没问题，但只要用TABLE()查返回值就报错；不带INSERT的存储过程完全没这个问题。

方案1：拆分DML和数据返回逻辑 #

把执行DML和返回数据拆成两个独立单元，避免冲突：

1. 写一个只做INSERT等DML的存储过程
2. 再写一个专门返回数据的表函数
3. 调用时先跑DML存储过程，再用TABLE()调用表函数拿结果

示例代码：

-- 仅执行DML的存储过程
CREATE OR REPLACE PROCEDURE RUN_INSERT()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    INSERT INTO target_table(col1, col2) VALUES ('v1', 'v2');
    RETURN 'INSERT_DONE';
END;
$$;

-- 仅返回数据的表函数
CREATE OR REPLACE FUNCTION GET_RESULT_DATA()
RETURNS TABLE(col1 VARCHAR, col2 VARCHAR)
LANGUAGE SQL
AS
$$
    SELECT col1, col2 FROM source_table WHERE filter_condition;
$$;

-- 调用流程
CALL RUN_INSERT();
SELECT * FROM TABLE(GET_RESULT_DATA());

方案2：用临时表传递结果 #

在存储过程里创建临时表，先执行INSERT，再把要返回的数据写入临时表，最后直接查临时表：

CREATE OR REPLACE PROCEDURE EXAMPLE_WITH_INSERT()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- 执行业务需要的INSERT
    INSERT INTO target_table(col1, col2) VALUES ('v1', 'v2');
    
    -- 创建临时表存返回数据
    CREATE OR REPLACE TEMPORARY TABLE temp_result(col1 VARCHAR, col2 VARCHAR);
    INSERT INTO temp_result SELECT col1, col2 FROM source_table WHERE filter_condition;
    
    RETURN 'SUCCESS';
END;
$$;

-- 调用后取结果
CALL EXAMPLE_WITH_INSERT();
SELECT * FROM temp_result;

方案3：结合RESULT_SCAN获取存储过程输出 #

如果存储过程内部用SELECT输出结果，调用后可以用RESULT_SCAN()拿到返回数据：

CREATE OR REPLACE PROCEDURE EXAMPLE_WITH_INSERT()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- 执行DML
    INSERT INTO target_table(col1, col2) VALUES ('v1', 'v2');
    
    -- 直接输出要返回的结果
    SELECT col1, col2 FROM source_table WHERE filter_condition;
    
    RETURN 'SUCCESS';
END;
$$;

-- 调用并获取结果
CALL EXAMPLE_WITH_INSERT();
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Snowflake对TABLE()调用的存储过程有硬性限制：不允许内部包含DML操作。因为TABLE()期望调用的是纯查询型的存储过程（只返回数据，不修改数据状态），而DML属于会改变数据状态的操作，触发了编译阶段的校验规则，所以直接报错。

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