Snowflake返回TABLE类型的SQL存储过程异常处理问题排查

问题分析 #

原存储过程的核心错误有两个：

1. 错误使用EXECUTE IMMEDIATE：你将拼接的错误描述字符串（如SQLCODE =-xxx, SQLERRM = ...）当作SQL语句执行，这不是合法的SQL指令，必然触发语法错误。
2. RETURNS TABLE定义不合法：不能留空括号，必须明确列结构，且需与UDTFGET_MEDICATION的返回列一致。

解决方案 #

以下提供两种实用的异常处理方案，可根据业务需求选择：

方案1：保持与UDTF返回结构兼容

假设GET_MEDICATION的返回结构为MEDICATION_ID INT, MEDICATION_NAME VARCHAR, DESCRIPTION VARCHAR，异常时返回包含错误信息的行：

CREATE OR REPLACE PROCEDURE MEDICATION_MODEL_NLS_Test(Language_key VARCHAR DEFAULT 'en')
RETURNS TABLE (MEDICATION_ID INT, MEDICATION_NAME VARCHAR, DESCRIPTION VARCHAR)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    res RESULTSET;
BEGIN
    res := (SELECT * FROM TABLE(GET_MEDICATION(:Language_key)));
    RETURN TABLE(res);
EXCEPTION
    WHEN STATEMENT_ERROR OR EXPRESSION_ERROR THEN
        -- 构造与UDTF结构一致的错误结果行
        res := (
            SELECT 
                NULL AS MEDICATION_ID,
                'EXCEPTION OCCURRED' AS MEDICATION_NAME,
                'SQLCODE = ' || SQLCODE || ', SQLERRM = ' || SQLERRM || ', SQLSTATE = ' || SQLSTATE AS DESCRIPTION
        );
        RETURN TABLE(res);
END
$$;

方案2：明确区分正常/异常数据

若需要清晰区分正常返回和异常信息，可扩展返回表的列定义：

CREATE OR REPLACE PROCEDURE MEDICATION_MODEL_NLS_Test(Language_key VARCHAR DEFAULT 'en')
RETURNS TABLE (
    IS_ERROR BOOLEAN,
    SQL_CODE INT,
    ERROR_MSG VARCHAR,
    SQL_STATE VARCHAR,
    MEDICATION_ID INT,
    MEDICATION_NAME VARCHAR,
    DESCRIPTION VARCHAR
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    res RESULTSET;
BEGIN
    res := (
        SELECT 
            FALSE AS IS_ERROR,
            NULL AS SQL_CODE,
            NULL AS ERROR_MSG,
            NULL AS SQL_STATE,
            MEDICATION_ID,
            MEDICATION_NAME,
            DESCRIPTION
        FROM TABLE(GET_MEDICATION(:Language_key))
    );
    RETURN TABLE(res);
EXCEPTION
    WHEN STATEMENT_ERROR OR EXPRESSION_ERROR THEN
        res := (
            SELECT 
                TRUE AS IS_ERROR,
                SQLCODE AS SQL_CODE,
                SQLERRM AS ERROR_MSG,
                SQLSTATE AS SQL_STATE,
                NULL AS MEDICATION_ID,
                NULL AS MEDICATION_NAME,
                NULL AS DESCRIPTION
        );
        RETURN TABLE(res);
END
$$;

关键注意点 #

• 必须确保RETURNS TABLE的列定义与实际返回结果集完全匹配，否则会触发运行时错误。
• EXECUTE IMMEDIATE仅用于动态执行合法SQL语句，不要用来处理错误信息拼接字符串。

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