Snowflake存储过程RETURNS TABLE报错及Power BI调用问题求助

问题描述 #

• Power BI Report Builder调用存储过程失败：
  我是Snowflake UI和Power BI Report Builder的新手，希望在Power BI Report Builder中构建报表，允许终端用户输入信息后，通过Snowflake存储过程生成的表格返回结果。已确认Snowflake的ODBC驱动可正常运行简单查询，但调用存储过程始终失败，有人建议需在存储过程的RETURNS TABLE()部分声明字段。
• Snowflake UI中带RETURNS TABLE()声明的存储过程报错：
  未定义RETURNS TABLE()字段时，存储过程可正常运行，但添加声明后调用会报错：

  Stored procedure execution error: data type of returned table does not match expected returned table type.
  测试发现问题出在RETURNS TABLE()中声明varchar字段，但相关资料显示应该支持，不清楚问题所在。

测试用例代码 #

CREATE OR REPLACE TABLE <schema>.TEST_TBL(a integer, b varchar, c varchar, d varchar);
INSERT INTO <schema>.TEST_TBL(a,b,c,d) VALUES
(1, 'Abigail', 'IA', '12345'),
(2, 'Brenda', 'CA', '66789'),
(3, 'Carter', 'PA', '01123');

CREATE OR REPLACE PROCEDURE <schema>.SP_Test("STATE" VARCHAR(2), "ZIP" VARCHAR(5))
RETURNS TABLE (a integer, b varchar)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
        res RESULTSET;
BEGIN
        RES: = (SELECT a, b from <schema>.TEST_TBL where c = :STATE and d = :ZIP);
        RETURN TABLE(res);
END;

CALL <schema>.SP_TEST('IA', '12345');

预期返回表中第一行，但实际触发上述错误。解决该存储过程问题后，将继续测试Power BI Report Builder的调用。

疑问总结 #

1. 存储过程的RETURNS TABLE()语句中变量声明是否有误？
2. 能否在Power BI Report Builder中调用Snowflake存储过程？

解决方案 #

问题1：Snowflake存储过程RETURNS TABLE()报错修复 #

报错原因是RETURNS TABLE()里的varchar未指定长度，Snowflake的SQL存储过程要求返回表的字段类型与查询结果的类型严格匹配。原表中b字段是无指定长度的varchar（Snowflake默认长度为16777216），但存储过程返回声明中b varchar未指定长度，导致类型不匹配。

修改存储过程的RETURNS TABLE部分，给varchar加上匹配的长度即可：

CREATE OR REPLACE PROCEDURE <schema>.SP_Test("STATE" VARCHAR(2), "ZIP" VARCHAR(5))
RETURNS TABLE (a integer, b varchar(16777216)) -- 可根据实际业务调整为更合适的长度，比如varchar(20)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
        res RESULTSET;
BEGIN
        RES: = (SELECT a, b from <schema>.TEST_TBL where c = :STATE and d = :ZIP);
        RETURN TABLE(res);
END;

问题2：Power BI Report Builder调用Snowflake存储过程 #

可以调用，需注意以下几点：

• 使用最新版本的Snowflake ODBC驱动，旧版本对存储过程返回表的支持存在缺陷。
• 在Power BI Report Builder的数据源设置中，开启“允许SQL查询”选项，使用CALL语句调用存储过程。
• 若存储过程需要参数，需在报表中创建对应参数，并将参数映射到存储过程的输入参数上。
• 存储过程必须明确声明RETURNS TABLE(...)，否则Power BI无法识别返回结果集的结构，这也是你之前调用失败的核心原因之一。

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