Snowflake存储过程子查询传参报错，咨询可行替代方案

在2025年1月调用Snowflake存储过程SP_1_test时，需要获取2024年12月的数据。原有逻辑中MONTH(to_date('2025-01-02'))-1会得到0，导致数据集为空，因此尝试用Union来处理，但执行调用语句时出现以下错误：

SQL compilation error: syntax error line 12 at position 0 unexpected 'select'. syntax error line 20 at position 4 unexpected 'as'.

调用语句如下：

CALL SP_1_test(
     select top 1 FISCAL_YEAR from (
    select FISCAL_YEAR
from  tmp_cal
         where FISCAL_PERIOD >= 1 and FISCAL_YEAR = YEAR(to_date('2025-01-02') ) 
         and FISCAL_PERIOD = MONTH(to_date('2025-01-02') )-1 
           and FISCAL_PERIOD_WEEK = 1 and FISCAL_PERIOD_DAY = 1
           Union all 
select    year(to_date(to_date('2025-01-02')))-1     as    FISCAL_YEAR
 ) as a /*this is param 1 valued as fiscal year 2020, 2021, etc.*/
,
select top 1 FISCAL_PERIOD from ( 
select FISCAL_PERIOD
from  tmp_cal
         where FISCAL_PERIOD >= 1 and FISCAL_YEAR = YEAR(to_date('2025-01-02') ) 
         and FISCAL_PERIOD = MONTH(to_date('2025-01-02') )-1 
           and FISCAL_PERIOD_WEEK = 1 and FISCAL_PERIOD_DAY = 1
                      union all 
select 12 as FISCAL_PERIOD         
)   as a /*this is param 2. A month valued between 1 to 12 */
           
           )

用户疑问：是否不能通过上述命名查询的方式为底层存储过程传递参数？若不可行，有什么替代解决方案？

1. 当前调用报错原因 #

Snowflake存储过程调用时，若参数是子查询，必须用外层括号将整个子查询包裹，否则SQL解析器无法识别这是一个合法的参数表达式。你当前的写法直接把select语句作为参数，没有加外层括号，导致语法解析失败。

2. 可行替代方案 #

方案一：给子查询添加外层括号

修改调用语句，将每个参数的子查询用括号包裹，即可正确解析：

CALL SP_1_test(
    (select top 1 FISCAL_YEAR from (
        select FISCAL_YEAR
        from tmp_cal
        where FISCAL_PERIOD >= 1 
          and FISCAL_YEAR = YEAR(to_date('2025-01-02')) 
          and FISCAL_PERIOD = MONTH(to_date('2025-01-02'))-1 
          and FISCAL_PERIOD_WEEK = 1 
          and FISCAL_PERIOD_DAY = 1
        Union all 
        select year(to_date('2025-01-02'))-1 as FISCAL_YEAR
    ) as a),
    (select top 1 FISCAL_PERIOD from ( 
        select FISCAL_PERIOD
        from tmp_cal
        where FISCAL_PERIOD >= 1 
          and FISCAL_YEAR = YEAR(to_date('2025-01-02')) 
          and FISCAL_PERIOD = MONTH(to_date('2025-01-02'))-1 
          and FISCAL_PERIOD_WEEK = 1 
          and FISCAL_PERIOD_DAY = 1
        union all 
        select 12 as FISCAL_PERIOD         
    ) as a)
);

方案二：先计算参数值，用变量传递

先通过单独查询得到所需的FISCAL_YEAR和FISCAL_PERIOD，赋值给变量后再调用存储过程，逻辑更清晰易维护：

-- 计算参数值
set v_fiscal_year = (
    select top 1 FISCAL_YEAR from (
        select FISCAL_YEAR
        from tmp_cal
        where FISCAL_PERIOD >= 1 
          and FISCAL_YEAR = YEAR(to_date('2025-01-02')) 
          and FISCAL_PERIOD = MONTH(to_date('2025-01-02'))-1 
          and FISCAL_PERIOD_WEEK = 1 
          and FISCAL_PERIOD_DAY = 1
        Union all 
        select year(to_date('2025-01-02'))-1 as FISCAL_YEAR
    ) as a
);

set v_fiscal_period = (
    select top 1 FISCAL_PERIOD from ( 
        select FISCAL_PERIOD
        from tmp_cal
        where FISCAL_PERIOD >= 1 
          and FISCAL_YEAR = YEAR(to_date('2025-01-02')) 
          and FISCAL_PERIOD = MONTH(to_date('2025-01-02'))-1 
          and FISCAL_PERIOD_WEEK = 1 
          and FISCAL_PERIOD_DAY = 1
        union all 
        select 12 as FISCAL_PERIOD         
    ) as a
);

-- 调用存储过程
CALL SP_1_test($v_fiscal_year, $v_fiscal_period);

方案三：简化月份计算逻辑

直接用日期函数计算上一个月的年和月，无需依赖tmp_cal表查询，避免原逻辑返回空值的问题：

-- 计算上一个月的日期
set v_last_month = DATEADD(MONTH, -1, to_date('2025-01-02'));
-- 提取年份和月份
set v_fiscal_year = EXTRACT(YEAR FROM $v_last_month);
set v_fiscal_period = EXTRACT(MONTH FROM $v_last_month);

-- 调用存储过程
CALL SP_1_test($v_fiscal_year, $v_fiscal_period);

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