编写带变量的Snowflake函数报错求助：语法错误排查

核心错误点分析 #

1. 变量声明与初始化错误：
   DECLARE块内不能直接用函数初始化变量，原代码time time TIME(:dateTime);语法完全错误，Snowflake变量声明格式为变量名 数据类型;，初始化需放到BEGIN块内用SET语句完成。另外time是Snowflake关键字，建议改名避免冲突。
2. INTO子句变量顺序错误：
   第一次SELECT的三个字段值应存入自定义变量，而非覆盖输入参数shiftCalendarID，原代码顺序错位导致逻辑、语法双重错误。
3. 未初始化变量提前引用：
   原代码用:curDay计算prvDay，但此时curDay尚未赋值，逻辑错误，应直接基于DATEDIFF的结果计算prvDay。
4. 变量引用符号错误：
   Snowflake函数内本地变量用:前缀，@前缀对应会话变量，原代码@shiftCalendarID属于误用。
5. TABLE函数未返回结果集：
   函数声明RETURNS table，但原代码未返回结果，需明确返回查询结果集。

修正后的完整代码 #

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)
RETURNS table (shiftID int)
AS
$$
DECLARE
    target_time TIME;
    curDay      int;
    prvDay      int;
    calID       int; -- 存储查询到的ShiftCalendarID
BEGIN
    -- 初始化时间变量
    SET target_time = TIME(:dateTime);

    -- 获取ShiftCalendar信息及计算curDay、prvDay
    SELECT TOP 1
        ID,
        DATEDIFF(day, BeginDate, :dateTime) % PeriodInDays + 1,
        (DATEDIFF(day, BeginDate, :dateTime) % PeriodInDays + 1 + PeriodInDays - 2) % PeriodInDays + 1
    INTO :calID, :curDay, :prvDay
    FROM RAW_CPMS_AAR.ShiftCalendar
    WHERE (ID = :shiftCalendarID)
        OR (:shiftCalendarID IS NULL AND Name = 'Factory' AND BeginDate <= :dateTime)
    ORDER BY BeginDate DESC;

    -- 返回对应的ShiftID结果集
    RETURN TABLE(
        SELECT ID AS shiftID
        FROM Shift
        WHERE ShiftCalendarID = :calID
            AND (
                (FromDay = :curDay AND FromTimeOfDay <= :target_time AND TillTimeOfDay > :target_time)
                OR (FromDay = :curDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= :target_time)
                OR (FromDay = :prvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay > :target_time)
            )
    );
END;
$$

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