Snowflake存储过程返回RESULTSET表时类型不匹配问题排查

问题现象 #

存储过程可正常创建，但使用CALL命令或在SELECT语句的FROM子句中调用时，触发错误：

Stored procedure execution error: data type of returned table does not match expected returned table type

已尝试的方案：

• 在DECLARE块定义res变量并赋值结果集
• 在BEGIN块执行查询后返回TABLE(res)
  两种方式均出现相同错误。

当前使用的存储过程代码：

CREATE OR REPLACE PROCEDURE "Online_Mobile_Activity"()
RETURNS TABLE ("ACCTINST" NUMBER(38,0), "BRANCH" NUMBER(38,0), "TRAN_DATE" DATE, "TRAN_TIME" TIME(9), "ACCOUNT" VARCHAR, "ACCT_TYP" VARCHAR, "TRAN_AMT" NUMBER(38,0), "TRAN_TYP" VARCHAR, "USER_ID" VARCHAR, "PRIMARY_OWNER" VARCHAR, "EMAIL_ADDRESS" VARCHAR, "NAME_ID" VARCHAR, "OPEN_DATE" DATE, "CLASS_CODE" VARCHAR, "PORTFOLIO" VARCHAR)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS
$$
DECLARE
    res RESULTSET DEFAULT (
    SELECT
        CASE 
            WHEN b."zone_description" = 'One' THEN 1
            WHEN b."zone_description" = 'Zero' THEN 0 
        END AS "ACCTINST",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'Zero' THEN 301 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'Zero' THEN 300
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'Zero' THEN 300 
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'Zero' THEN 301 
        END AS "Branch",
        DATE(a."session_date") AS "tran_date",
        TIME(a."session_date") AS "tran_time",
        acct."Account Number" AS "ACCOUNT",
        acct."FS Group" AS "ACCT_TYP",
        0 AS "TRAN_AMT",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' THEN 'Mobile Log In' 
            WHEN a."sessionanalysis_category" = 'Monitor' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Desktop' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Tablet' THEN 'Mobile Log In' 
        END AS "TRAN_TYP",
        a."user_id",
        cif."Name" AS "Primary_Owner",
        e."Contact Info" AS "email_address",
        '' AS "name_id",
        acct."Date Opened" AS "open_date",
        acct."Class Code" AS "class_code",
        acct."Portfolio" AS "portfolio"
    FROM "PUBLISH"."Q2"."v_LogonActivity" a
        LEFT JOIN "UserData" b ON a."user_id" = b."user_id"
        LEFT JOIN "UserAccountData" d ON a."user_id" = d."user_id"
        LEFT JOIN "CIF_Name" cif ON d."CIFInternal" = cif."Tax ID Number"
        LEFT JOIN "Portfolios" p ON cif."Name ID" = p."Name 1 ID"
        LEFT JOIN "Email" e ON cif."Name ID" = e."Name ID"
        LEFT JOIN "Total_Deposits_Active" acct ON p."Portfolio Key" = acct."Portfolio Key"
    WHERE DATEDIFF(day, a."session_date", CURRENT_DATE()) = 2
        AND e."Contact Info" IS NOT NULL
        AND acct."Account Number" IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY a."user_id" ORDER BY a."session_date") = 1);
BEGIN
    RETURN TABLE(res);
END;
$$;

问题原因分析 #

1. 列名大小写不匹配：存储过程返回定义中的列名是全大写（如"BRANCH"），但查询中部分列的别名是大小写混合（如"Branch"、"Primary_Owner"），Snowflake对带引号的标识符区分大小写，导致返回列名与定义不匹配。
2. 数据类型隐式转换差异：
   • 查询中0 AS "TRAN_AMT"默认解析为NUMBER(1,0)，与返回定义的NUMBER(38,0)精度不匹配。
   • '' AS "name_id"默认是VARCHAR(1)，与返回定义的VARCHAR（无长度）存在类型兼容风险。
3. 未显式指定列别名：a."user_id"未指定别名，默认列名为小写user_id，但返回定义中是大写"USER_ID"，导致列名不匹配。

解决方案 #

方案1：修正列名与数据类型，确保完全匹配 #

调整查询中的列别名使其与返回表定义完全一致，同时显式转换常量值的类型：

CREATE OR REPLACE PROCEDURE "Online_Mobile_Activity"()
RETURNS TABLE ("ACCTINST" NUMBER(38,0), "BRANCH" NUMBER(38,0), "TRAN_DATE" DATE, "TRAN_TIME" TIME(9), "ACCOUNT" VARCHAR, "ACCT_TYP" VARCHAR, "TRAN_AMT" NUMBER(38,0), "TRAN_TYP" VARCHAR, "USER_ID" VARCHAR, "PRIMARY_OWNER" VARCHAR, "EMAIL_ADDRESS" VARCHAR, "NAME_ID" VARCHAR, "OPEN_DATE" DATE, "CLASS_CODE" VARCHAR, "PORTFOLIO" VARCHAR)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS
$$
DECLARE
    res RESULTSET DEFAULT (
    SELECT
        CASE 
            WHEN b."zone_description" = 'One' THEN 1::NUMBER(38,0)
            WHEN b."zone_description" = 'Zero' THEN 0::NUMBER(38,0) 
        END AS "ACCTINST",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'One' THEN 311::NUMBER(38,0) 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'One' THEN 310::NUMBER(38,0)
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'One' THEN 310::NUMBER(38,0)
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'One' THEN 311::NUMBER(38,0) 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'Zero' THEN 301::NUMBER(38,0) 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'Zero' THEN 300::NUMBER(38,0)
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'Zero' THEN 300::NUMBER(38,0) 
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'Zero' THEN 301::NUMBER(38,0) 
        END AS "BRANCH",
        DATE(a."session_date") AS "TRAN_DATE",
        TIME(a."session_date") AS "TRAN_TIME",
        acct."Account Number" AS "ACCOUNT",
        acct."FS Group" AS "ACCT_TYP",
        0::NUMBER(38,0) AS "TRAN_AMT",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' THEN 'Mobile Log In' 
            WHEN a."sessionanalysis_category" = 'Monitor' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Desktop' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Tablet' THEN 'Mobile Log In' 
        END AS "TRAN_TYP",
        a."user_id" AS "USER_ID",
        cif."Name" AS "PRIMARY_OWNER",
        e."Contact Info" AS "EMAIL_ADDRESS",
        ''::VARCHAR AS "NAME_ID",
        acct."Date Opened" AS "OPEN_DATE",
        acct."Class Code" AS "CLASS_CODE",
        acct."Portfolio" AS "PORTFOLIO"
    FROM "PUBLISH"."Q2"."v_LogonActivity" a
        LEFT JOIN "UserData" b ON a."user_id" = b."user_id"
        LEFT JOIN "UserAccountData" d ON a."user_id" = d."user_id"
        LEFT JOIN "CIF_Name" cif ON d."CIFInternal" = cif."Tax ID Number"
        LEFT JOIN "Portfolios" p ON cif."Name ID" = p."Name 1 ID"
        LEFT JOIN "Email" e ON cif."Name ID" = e."Name ID"
        LEFT JOIN "Total_Deposits_Active" acct ON p."Portfolio Key" = acct."Portfolio Key"
    WHERE DATEDIFF(day, a."session_date", CURRENT_DATE()) = 2
        AND e."Contact Info" IS NOT NULL
        AND acct."Account Number" IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY a."user_id" ORDER BY a."session_date") = 1);
BEGIN
    RETURN TABLE(res);
END;
$$;

方案2：改用表值函数（更适合返回结果集场景） #

如果仅需返回查询结果，表值函数比存储过程更简洁，能减少类型匹配问题：

CREATE OR REPLACE FUNCTION "Online_Mobile_Activity"()
RETURNS TABLE ("ACCTINST" NUMBER(38,0), "BRANCH" NUMBER(38,0), "TRAN_DATE" DATE, "TRAN_TIME" TIME(9), "ACCOUNT" VARCHAR, "ACCT_TYP" VARCHAR, "TRAN_AMT" NUMBER(38,0), "TRAN_TYP" VARCHAR, "USER_ID" VARCHAR, "PRIMARY_OWNER" VARCHAR, "EMAIL_ADDRESS" VARCHAR, "NAME_ID" VARCHAR, "OPEN_DATE" DATE, "CLASS_CODE" VARCHAR, "PORTFOLIO" VARCHAR)
LANGUAGE SQL
STRICT
AS
$$
SELECT
    CASE 
        WHEN b."zone_description" = 'One' THEN 1::NUMBER(38,0)
        WHEN b."zone_description" = 'Zero' THEN 0::NUMBER(38,0) 
    END AS "ACCTINST",
    CASE 
        WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'One' THEN 311::NUMBER(38,0) 
        WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'One' THEN 310::NUMBER(38,0)
        WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'One' THEN 310::NUMBER(38,0)
        WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'One' THEN 311::NUMBER(38,0) 
        WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'Zero' THEN 301::NUMBER(38,0) 
        WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'Zero' THEN 300::NUMBER(38,0)
        WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'Zero' THEN 300::NUMBER(38,0) 
        WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'Zero' THEN 301::NUMBER(38,0) 
    END AS "BRANCH",
    DATE(a."session_date") AS "TRAN_DATE",
    TIME(a."session_date") AS "TRAN_TIME",
    acct."Account Number" AS "ACCOUNT",
    acct."FS Group" AS "ACCT_TYP",
    0::NUMBER(38,0) AS "TRAN_AMT",
    CASE 
        WHEN a."sessionanalysis_category" = 'Phone' THEN 'Mobile Log In' 
        WHEN a."sessionanalysis_category" = 'Monitor' THEN 'Online Log In' 
        WHEN a."sessionanalysis_category" = 'Desktop' THEN 'Online Log In' 
        WHEN a."sessionanalysis_category" = 'Tablet' THEN 'Mobile Log In' 
    END AS "TRAN_TYP",
    a."user_id" AS "USER_ID",
    cif."Name" AS "PRIMARY_OWNER",
    e."Contact Info" AS "EMAIL_ADDRESS",
    ''::VARCHAR AS "NAME_ID",
    acct."Date Opened" AS "OPEN_DATE",
    acct."Class Code" AS "CLASS_CODE",
    acct."Portfolio" AS "PORTFOLIO"
FROM "PUBLISH"."Q2"."v_LogonActivity" a
    LEFT JOIN "UserData" b ON a."user_id" = b."user_id"
    LEFT JOIN "UserAccountData" d ON a."user_id" = d."user_id"
    LEFT JOIN "CIF_Name" cif ON d."CIFInternal" = cif."Tax ID Number"
    LEFT JOIN "Portfolios" p ON cif."Name ID" = p."Name 1 ID"
    LEFT JOIN "Email" e ON cif."Name ID" = e."Name ID"
    LEFT JOIN "Total_Deposits_Active" acct ON p."Portfolio Key" = acct."Portfolio Key"
WHERE DATEDIFF(day, a."session_date", CURRENT_DATE()) = 2
    AND e."Contact Info" IS NOT NULL
    AND acct."Account Number" IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY a."user_id" ORDER BY a."session_date") = 1
$$;

调用方式：

SELECT * FROM TABLE("Online_Mobile_Activity"());

关键注意事项 #

• 带引号的标识符在Snowflake中区分大小写，返回表定义的列名与查询结果的列别名必须完全一致。
• 常量值需显式转换为目标数据类型，避免隐式转换导致的类型不匹配。
• 单纯返回查询结果时，优先使用表值函数，语法更简洁，类型匹配问题更少。

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