Snowflake存储过程事务内使用ResultSet报错排查

原本可正常执行的Snowflake SQL存储过程（代码如下），在添加BEGIN TRANSACTION事务控制语句后，触发如下编译错误：

SQL compilation error: syntax error line 18 at position 4 unexpected 'RES'.

原正常执行的存储过程代码 #

CREATE OR REPLACE PROCEDURE FAIR_PROC(
    DATABASE_REF VARCHAR
    , RUN_ID INTEGER
    , TASKID VARCHAR
    , RECORD_UPDATED NUMBER
    , RECORD_INSERTED NUMBER
    , RECORD_DELETED NUMBER)
RETURNS VARCHAR 
LANGUAGE SQL 
AS
DECLARE 
V_PROCEDURE_NAME VARCHAR(50) := 'FAIR_PROC';
V_TASK_METRICS_TBL_REF VARCHAR(50);
BEGIN
    V_TASK_METRICS_TBL_REF := :DATABASE_REF || '.METADATA_OPS.TASK_METRICS';
    LET RES RESULTSET;
    RES := (INSERT INTO IDENTIFIER(:V_TASK_METRICS_TBL_REF)(RUN_ID
        , TASKID 
        , RECORD_UPDATED
        , RECORD_INSERTED
        , RECORD_DELETED) 
    VALUES(:RUN_ID
        , :TASKID 
        , :RECORD_UPDATED
        , :RECORD_INSERTED
        , :RECORD_DELETED));
    RETURN 'SUCCESS';
END;

报错的存储过程代码 #

CREATE OR REPLACE PROCEDURE FAIR_PROC(
    DATABASE_REF VARCHAR
    , RUN_ID INTEGER
    , TASKID VARCHAR
    , RECORD_UPDATED NUMBER
    , RECORD_INSERTED NUMBER
    , RECORD_DELETED NUMBER)
RETURNS VARCHAR 
LANGUAGE SQL 
AS
DECLARE 
V_PROCEDURE_NAME VARCHAR(50) := 'FAIR_PROC';
V_TASK_METRICS_TBL_REF VARCHAR(50);
BEGIN
    V_TASK_METRICS_TBL_REF := :DATABASE_REF || '.METADATA_OPS.TASK_METRICS';
    LET RES RESULTSET;
    BEGIN TRANSACTION
    RES := (INSERT INTO IDENTIFIER(:V_TASK_METRICS_TBL_REF)(RUN_ID
        , TASKID 
        , RECORD_UPDATED
        , RECORD_INSERTED
        , RECORD_DELETED) 
    VALUES(:RUN_ID
        , :TASKID 
        , :RECORD_UPDATED
        , :RECORD_INSERTED
        , :RECORD_DELETED));
    COMMIT;
    RETURN 'SUCCESS';
EXCEPTION
WHEN OTHER THEN
     ROLLBACK;
END;

报错原因 #

1. 语法缺失：BEGIN TRANSACTION语句末尾未添加分号，导致Snowflake解析器无法正确识别事务语句的结束，后续的RES := ...赋值语句被错误地合并到事务语句中，触发语法冲突。
2. 事务控制语句在Snowflake SQL存储过程中必须作为独立的完整语句存在，必须以分号结尾，否则会破坏整个存储过程的语法结构。

解决方法 #

1. 修正事务语句的语法 #

在BEGIN TRANSACTION末尾添加分号，确保每个语句独立完整。修正后的存储过程代码如下：

CREATE OR REPLACE PROCEDURE FAIR_PROC(
    DATABASE_REF VARCHAR
    , RUN_ID INTEGER
    , TASKID VARCHAR
    , RECORD_UPDATED NUMBER
    , RECORD_INSERTED NUMBER
    , RECORD_DELETED NUMBER)
RETURNS VARCHAR 
LANGUAGE SQL 
AS
DECLARE 
V_PROCEDURE_NAME VARCHAR(50) := 'FAIR_PROC';
V_TASK_METRICS_TBL_REF VARCHAR(50);
BEGIN
    V_TASK_METRICS_TBL_REF := :DATABASE_REF || '.METADATA_OPS.TASK_METRICS';
    LET RES RESULTSET;
    BEGIN TRANSACTION; -- 此处添加分号
    RES := (INSERT INTO IDENTIFIER(:V_TASK_METRICS_TBL_REF)(RUN_ID
        , TASKID 
        , RECORD_UPDATED
        , RECORD_INSERTED
        , RECORD_DELETED) 
    VALUES(:RUN_ID
        , :TASKID 
        , :RECORD_UPDATED
        , :RECORD_INSERTED
        , :RECORD_DELETED));
    COMMIT;
    RETURN 'SUCCESS';
EXCEPTION
WHEN OTHER THEN
     ROLLBACK;
END;

2. 针对MERGE收集指标的实际场景调整 #

如果是在MERGE操作中收集更新、插入记录的指标，可以通过RETURNING子句捕获结果集，再赋值给ResultSet变量，同时确保事务语句的完整性。示例如下：

CREATE OR REPLACE PROCEDURE FAIR_MERGE_PROC(
    DATABASE_REF VARCHAR
    , RUN_ID INTEGER
    , TASKID VARCHAR)
RETURNS VARCHAR 
LANGUAGE SQL 
AS
DECLARE 
V_MAIN_TBL_REF VARCHAR(50) := :DATABASE_REF || '.DATA.MAIN_TABLE';
V_STAGING_TBL_REF VARCHAR(50) := :DATABASE_REF || '.DATA.STAGING_TABLE';
V_METRICS_TBL_REF VARCHAR(50) := :DATABASE_REF || '.METADATA_OPS.TASK_METRICS';
LET MERGE_RES RESULTSET;
BEGIN
    BEGIN TRANSACTION;
    
    -- MERGE操作并返回变更记录
    MERGE_RES := (MERGE INTO IDENTIFIER(:V_MAIN_TBL_REF) AS TGT
                  USING IDENTIFIER(:V_STAGING_TBL_REF) AS SRC
                  ON TGT.ID = SRC.ID
                  WHEN MATCHED THEN UPDATE SET TGT.VALUE = SRC.VALUE
                  WHEN NOT MATCHED THEN INSERT (ID, VALUE) VALUES (SRC.ID, SRC.VALUE)
                  RETURNING ACTION, ID); -- 返回操作类型（INSERT/UPDATE）
    
    -- 统计指标并写入任务度量表
    INSERT INTO IDENTIFIER(:V_METRICS_TBL_REF)(RUN_ID, TASKID, RECORD_UPDATED, RECORD_INSERTED)
    SELECT :RUN_ID, :TASKID,
           COUNT(CASE WHEN ACTION = 'UPDATE' THEN 1 END),
           COUNT(CASE WHEN ACTION = 'INSERT' THEN 1 END)
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    
    COMMIT;
    RETURN 'SUCCESS';
EXCEPTION
WHEN OTHER THEN
     ROLLBACK;
     RETURN 'FAILED: ' || SQLERRM;
END;

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