如何将Snowflake SQL存储过程的返回值赋值给变量？

我已在Snowflake中创建了一个返回表行数的存储过程，代码如下：

CREATE OR REPLACE PROCEDURE CDS_EXTRACT_CHECK_ROWCOUNT(STAGE_DATABASE varchar, STAGE_SCHEMA varchar, STAGE_TABLE varchar)
RETURNS table (a int)
LANGUAGE SQL
AS
    DECLARE
    stmt string;
    res resultset;
    rowcount int;
    BEGIN
        stmt := 'SELECT COUNT(*) FROM ' || :STAGE_DATABASE || '.' || :STAGE_SCHEMA || '.' || :STAGE_TABLE || ';'; 
        res := (EXECUTE IMMEDIATE :stmt);
        RETURN TABLE(res);
    END
;

调用该存储过程后的结果：

我希望在另一个存储过程中执行该存储过程，并将其返回值存储到变量中，尝试的代码如下：

rowcount := CALL CDS_EXTRACT_CHECK_ROWCOUNT(:STAGE_DATABASE, :STAGE_SCHEMA, :STAGE_TABLE);

但这样无法实现，恳请赐教。

解决方案 #

在Snowflake中，直接用赋值语句接收返回表类型的存储过程结果是行不通的，你可以通过以下两种方式实现需求：

方式1：使用RESULT_SCAN获取调用结果 #

调用存储过程后，通过RESULT_SCAN查询上一次执行的结果并赋值给变量：

CREATE OR REPLACE PROCEDURE YOUR_OTHER_PROCEDURE(STAGE_DATABASE varchar, STAGE_SCHEMA varchar, STAGE_TABLE varchar)
LANGUAGE SQL
AS
    DECLARE
        rowcount int;
    BEGIN
        -- 调用返回表的存储过程
        CALL CDS_EXTRACT_CHECK_ROWCOUNT(:STAGE_DATABASE, :STAGE_SCHEMA, :STAGE_TABLE);
        -- 通过RESULT_SCAN获取结果并存入变量
        SELECT a INTO :rowcount FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
        
        -- 可在此处使用rowcount变量执行后续操作，例如打印结果
        SELECT :rowcount AS table_rowcount;
        RETURN :rowcount;
    END
;

方式2：修改原存储过程返回标量值 #

如果不需要原存储过程返回表类型，可直接修改它返回标量整数，这样就能直接赋值：

-- 修改原存储过程返回标量
CREATE OR REPLACE PROCEDURE CDS_EXTRACT_CHECK_ROWCOUNT(STAGE_DATABASE varchar, STAGE_SCHEMA varchar, STAGE_TABLE varchar)
RETURNS int
LANGUAGE SQL
AS
    DECLARE
        stmt string;
        rowcount int;
    BEGIN
        stmt := 'SELECT COUNT(*) FROM ' || :STAGE_DATABASE || '.' || :STAGE_SCHEMA || '.' || :STAGE_TABLE || ';'; 
        EXECUTE IMMEDIATE :stmt INTO :rowcount;
        RETURN :rowcount;
    END
;

-- 在另一个存储过程中直接赋值
CREATE OR REPLACE PROCEDURE YOUR_OTHER_PROCEDURE(STAGE_DATABASE varchar, STAGE_SCHEMA varchar, STAGE_TABLE varchar)
LANGUAGE SQL
AS
    DECLARE
        rowcount int;
    BEGIN
        rowcount := CALL CDS_EXTRACT_CHECK_ROWCOUNT(:STAGE_DATABASE, :STAGE_SCHEMA, :STAGE_TABLE);
        
        -- 后续操作
        SELECT :rowcount AS table_rowcount;
        RETURN :rowcount;
    END
;

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