Snowflake存储过程（EXECUTE AS CALLER）设置会话变量报错原因

问题场景 #

在Snowflake的SQL存储过程中设置EXECUTE AS CALLER后，执行set T = 0并立刻引用$T时会抛出错误：Error: Session variable '$T' does not exist (line 5)。但预先在会话中设置变量T后，存储过程就能正常运行。原始存储过程代码如下：

create or replace procedure test()
returns varchar()
language SQL
EXECUTE AS CALLER
as
$$

begin
    set T = 0;
    select $T;
    return 'Done !';
    end;
    
$$;

call test();

原因分析 #

SQL存储过程中的静态SQL语句（如上述的select $T）会在编译阶段解析会话变量。此时set T = 0还未执行，会话中不存在$T，因此编译时直接报错。若预先在会话中设置T，编译阶段变量已存在，就能正常通过检查。

解决方法 #

方法一：使用存储过程局部变量

改用存储过程内部的局部变量替代会话变量，避免依赖会话状态，同时局部变量的作用域仅限于存储过程内部，更安全可控。

create or replace procedure test()
returns varchar()
language SQL
EXECUTE AS CALLER
as
$$
declare
    T integer default 0; -- 声明局部变量并初始化
begin
    select T; -- 直接引用局部变量，无需$前缀
    return 'Done !';
end;
$$;

call test();

方法二：用动态SQL执行变量引用

如果必须使用会话变量，可通过execute immediate执行动态SQL，动态SQL会在运行阶段解析变量，此时set T = 0已经执行，会话变量$T已存在，不会报错。

create or replace procedure test()
returns varchar()
language SQL
EXECUTE AS CALLER
as
$$
begin
    set T = 0;
    execute immediate 'select $T'; -- 动态SQL延迟解析变量
    return 'Done !';
end;
$$;

call test();

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