Snowflake存储过程中设置变量后在动态SQL调用报错的解决咨询

问题分析 #

你的代码存在两个关键问题：

• 变量声明不匹配：声明了create_query但实际使用的是未声明的select_query，会引发变量未定义错误；
• 动态SQL绑定变量未传递参数：动态SQL中的:MISSING_DEALER没有通过USING子句关联存储过程内的变量，导致报错"Bind variable :MISSING_DEALER not set"。

修正后的代码 #

create or replace procedure sp1()
returns table (dealer_id varchar, dealershipgroup_name varchar)
language sql
as
$$
declare
  select_query varchar; -- 修正变量名，和后续使用保持一致
  res resultset;
  MISSING_DEALER NUMBER(38,0) default 0;
begin
  MISSING_DEALER := 100;
  select_query := 'WITH CTE AS(
                                SELECT dealer_id, 
                                CASE WHEN dealer_id=:MISSING_DEALER then ''Abc''
                                WHEN dealershipgroup IS NULL then '''' 
                                ELSE dealershipgroup end as dealershipgroup_name FROM TBL )
                     
                    select dealer_id, dealershipgroup_name from CTE';
                     
  res := (execute immediate :select_query USING :MISSING_DEALER); -- 通过USING传递绑定变量的值
  return table(res);
  
end;
$$; 

call sp1();

关键修改说明 #

1. 变量名修正：将declare中的create_query改为select_query，和后续赋值的变量名统一，避免未定义错误；
2. 传递绑定变量：在execute immediate后添加USING :MISSING_DEALER，把存储过程内的变量值传递给动态SQL中的绑定变量；
3. 字段别名匹配：CTE中的字段别名改为dealershipgroup_name，和存储过程返回表的字段名保持一致，避免返回结构不匹配的问题。

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