如何创建返回表的Snowflake存储过程？

问题梳理 #

我看过很多Snowflake存储过程示例，但多数更像函数，仅返回单个数值。找到声称能返回完整数据表的示例又过于简略，不足以指导实操。我有一个可正常运行的查询，使用声明变量时能返回数据表，但放入存储过程框架后持续报错，常见错误为Unexpected EOF（意外文件结束）或WITH语句相关错误。尝试过移除语言声明、调整分号位置等方法，均无法成功部署运行。公司资深人员称Snowflake无法实现这类功能，但我难以相信它存在如此大的局限性，求解决思路。

错误点排查 #

你的存储过程存在几个关键语法问题，导致解析报错：

• RESULTSET变量声明时，DEFAULT括号内开头多了一个多余的分号，破坏了语法结构
• Snowflake SQL存储过程中，引用输入参数不需要加$前缀，直接使用参数名称即可
• RETURN语句中引用的变量名大小写不匹配（DECLARE的是results，你写的是RESULTSET，Snowflake对标识符大小写敏感）

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE TEST.TRANSACTIONS_GO(
    ParamServiceArea STRING 
    ,ParamLocation STRING 
    ,ParamDept STRING
    ,ParamStartDate STRING
    ,ParamEndDate STRING
    ,ParamCStartDate STRING
    ,ParamCEndDate STRING
)
RETURNS TABLE(ServAreaId STRING
                ,ServAreaName STRING
                ,LocId STRING)  
LANGUAGE SQL
AS
DECLARE
    results RESULTSET DEFAULT (
        WITH SAtag AS (
            SELECT sa.value AS SERV_AREA_ID
            FROM table(SPLIT_TO_TABLE(ParamServiceArea,',')) sa
        )
        , Loctag AS (
            SELECT CAST(l.value AS INT) AS LOC_ID
            FROM table(SPLIT_TO_TABLE(ParamLocation, ',')) l
        )
        , Deptag AS (
            SELECT CAST(d.value AS INT) AS DEPT_ID
            FROM table(SPLIT_TO_TABLE(ParamDept, ',')) d
        )
        ,START_DATE AS (
            SELECT CALENDAR_DATE AS START_DATE
            FROM DIM_DATE 
            WHERE CALENDAR_DATE = (SELECT DATE_CALCULATE(ParamCStartDate, ParamStartDate))
        ), END_DATE AS (
            SELECT CALENDAR_DATE AS END_DATE 
            FROM DIM_DATE 
            WHERE CALENDAR_DATE = (SELECT DATE_CALCULATE(ParamCEndDate, ParamEndDate))
        )
        SELECT TDL.SERV_AREA_ID AS ServAreaId
                ,TDL.SERV_AREA_NAME AS ServAreaName
                ,TDL.LOC_ID AS LocId
        FROM TRANSACTIONS_MAIN TDL
        WHERE TDL.POST_DATE BETWEEN (SELECT START_DATE FROM START_DATE)
                        AND (SELECT END_DATE FROM END_DATE)
            AND TDL.SERV_AREA_ID IN (SELECT CAST(SERV_AREA_ID AS INT) FROM SAtag)
            AND TDL.LOC_ID IN (SELECT LOC_ID FROM Loctag)
            AND TDL.DEPT_ID IN (SELECT DEPT_ID FROM Deptag)
        GROUP BY TDL.SERV_AREA_ID
                ,TDL.SERV_AREA_NAME
                ,TDL.LOC_ID
    );
BEGIN
    RETURN TABLE(results);
END;

调用验证 #

部署成功后，可通过以下方式调用存储过程：

CALL TEST.TRANSACTIONS_GO('1,2', '3,4', '5,6', '-30 days', '-1 days', '2024-01-01', '2024-01-31');

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