如何在Snowflake存储过程中引用外部定义的变量？

用户编写了Snowflake存储过程调用Document AI构建，同时定义了会话变量DOC_AI_VERSION，并希望将DOCAI_BUILD_NAME也设置为变量，但在存储过程中直接使用$DOC_AI_VERSION时报错：$DOC_AI_VERSION' does not exist。

原存储过程代码：

CREATE OR REPLACE PROCEDURE myproc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS

BEGIN

        CREATE OR REPLACE TEMPORARY TABLE my_table AS
        SELECT
            FILE_NAME,
            DOCAI_BUILD_NAME!PREDICT(
                GET_PRESIGNED_URL(@DOCS, FILE_NAME), 3
            ) AS pred
        FROM mytable;


    RETURN 'OK';
END;

触发任务代码：

CREATE OR REPLACE TASK mytask
WAREHOUSE = mywarehouse
SCHEDULE = '1 minute'
USER_TASK_TIMEOUT_MS = 86400000
WHEN SYSTEM$STREAM_HAS_DATA('mystream')
AS
    CALL myproc();

已定义的会话变量：

SET DOC_AI_VERSION = 3;

Snowflake的SQL存储过程无法直接通过$变量名的方式访问会话变量，同时DOCAI_BUILD_NAME作为UDF调用的标识符，需要结合动态SQL处理，具体方案如下：

1. 正确读取会话变量 #

在SQL存储过程中，使用CURRENT_SESSION_VARIABLE('变量名')函数替代$前缀，来获取会话变量的值。

2. 用动态SQL处理标识符变量 #

由于DOCAI_BUILD_NAME是需要拼接进UDF调用的标识符，必须通过动态SQL构建查询语句，同时用IDENTIFIER()函数安全引用变量，避免语法错误与SQL注入风险。

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE myproc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
    -- 读取会话变量
    LET docai_version STRING := CURRENT_SESSION_VARIABLE('DOC_AI_VERSION');
    LET docai_build_name STRING := CURRENT_SESSION_VARIABLE('DOCAI_BUILD_NAME');

    -- 构建动态SQL语句
    LET sql_stmt STRING := '
        CREATE OR REPLACE TEMPORARY TABLE my_table AS
        SELECT
            FILE_NAME,
            IDENTIFIER(:1)!PREDICT(
                GET_PRESIGNED_URL(@DOCS, FILE_NAME), :2
            ) AS pred
        FROM mytable;
    ';

    -- 执行动态SQL，传入变量参数
    EXECUTE IMMEDIATE sql_stmt USING (docai_build_name, docai_version);

    RETURN 'OK';
END;

3. 确保任务执行时变量生效 #

任务在独立会话中执行，直接设置的会话变量不会自动生效，可通过两种方式解决：

方式一：任务中先设置变量再调用存储过程 #

CREATE OR REPLACE TASK mytask
WAREHOUSE = mywarehouse
SCHEDULE = '1 minute'
USER_TASK_TIMEOUT_MS = 86400000
WHEN SYSTEM$STREAM_HAS_DATA('mystream')
AS
BEGIN
    SET DOC_AI_VERSION = 3;
    SET DOCAI_BUILD_NAME = '你的构建名称';
    CALL myproc();
END;

方式二：将变量作为参数传入存储过程（更推荐） #

-- 修改存储过程以接收参数
CREATE OR REPLACE PROCEDURE myproc(docai_build_name STRING, docai_version INT)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
    LET sql_stmt STRING := '
        CREATE OR REPLACE TEMPORARY TABLE my_table AS
        SELECT
            FILE_NAME,
            IDENTIFIER(:1)!PREDICT(
                GET_PRESIGNED_URL(@DOCS, FILE_NAME), :2
            ) AS pred
        FROM mytable;
    ';

    EXECUTE IMMEDIATE sql_stmt USING (docai_build_name, docai_version);

    RETURN 'OK';
END;

-- 修改任务调用时传入参数
CREATE OR REPLACE TASK mytask
WAREHOUSE = mywarehouse
SCHEDULE = '1 minute'
USER_TASK_TIMEOUT_MS = 86400000
WHEN SYSTEM$STREAM_HAS_DATA('mystream')
AS
    CALL myproc('你的构建名称', 3);

此方式可避免会话变量在任务会话中失效的问题，代码逻辑更清晰可控。

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