如何在Snowflake存储过程中正确传递参数？参数替换异常排查

问题原因 #

存储过程中，文件名拼接部分的||Company_name||正确引用了存储过程参数，能替换为传入的值；但WHERE子句里的UPPER(''Company_name'')是将字符串字面量Company_name转大写，并未使用传入的参数值——这里的双单引号只是转义生成SQL中的单引号，最终执行的SQL里是UPPER('Company_name')，而非参数对应的实际业务值。

解决方法 #

方法1：字符串拼接替换参数（需处理单引号转义）

修改WHERE子句中的参数引用方式，将字符串字面量替换为参数拼接，确保生成的SQL中使用实际传入的参数值：

CREATE OR REPLACE PROCEDURE DEVELOPMENT.PUBLIC.PDL_SKILLS_DATA_SP(Company_name VARCHAR)    
    returns varchar
    LANGUAGE SQL
    as 
    $$ 
    DECLARE
       query STRING;
       date_str STRING;
       time_str STRING;
    BEGIN 
        SELECT TO_CHAR(current_date,'ddmmyyyy') INTO :date_str;
        SELECT TO_CHAR(CURRENT_TIME,'HH24MMSS') INTO :time_str;
        QUERY := 'copy into @pdl_skills_data_dump/pdl_raw_data/skills_pdl_data_'||Company_name ||'_'||date_str || time_str ||'.csv from ( SELECT s.employee_id as EMPLOYEE_ID,
             s.skill as SKILL
    FROM DEVELOPMENT.TENANT__ALL_COMPANIES.jobs_with_rolemapping j
    JOIN DEVELOPMENT.TENANT__ALL_COMPANIES.skills s ON j.employee_id = s.EMPLOYEE_ID
    WHERE UPPER(COMPANY_NAME)=UPPER('''||:Company_name||''')
      AND end_date IS NULL
      AND start_date IS NOT NULL)
      file_format=(type=csv field_delimiter=\',\' compression=none ENCODING=UTF8
      field_optionally_enclosed_by= \'"\')
      header = true
      single = true
      OVERWRITE = TRUE
      max_file_size = 4900000000;';
                     
       EXECUTE IMMEDIATE :QUERY;
       
       RETURN :QUERY;
    end;
    $$;

核心修改点：将WHERE UPPER(COMPANY_NAME)=UPPER(''Company_name'')改为WHERE UPPER(COMPANY_NAME)=UPPER('''||:Company_name||''')，通过字符串拼接把参数值插入到SQL语句中，同时用三个单引号实现转义，确保生成的SQL里参数值被单引号包裹。

方法2：使用绑定变量（推荐，更安全）

利用Snowflake的绑定变量机制，避免手动处理单引号转义，同时防止SQL注入风险：

CREATE OR REPLACE PROCEDURE DEVELOPMENT.PUBLIC.PDL_SKILLS_DATA_SP(Company_name VARCHAR)    
    returns varchar
    LANGUAGE SQL
    as 
    $$ 
    DECLARE
       query STRING;
       date_str STRING;
       time_str STRING;
    BEGIN 
        SELECT TO_CHAR(current_date,'ddmmyyyy') INTO :date_str;
        SELECT TO_CHAR(CURRENT_TIME,'HH24MMSS') INTO :time_str;
        QUERY := 'copy into @pdl_skills_data_dump/pdl_raw_data/skills_pdl_data_'||Company_name ||'_'||date_str || time_str ||'.csv from ( SELECT s.employee_id as EMPLOYEE_ID,
             s.skill as SKILL
    FROM DEVELOPMENT.TENANT__ALL_COMPANIES.jobs_with_rolemapping j
    JOIN DEVELOPMENT.TENANT__ALL_COMPANIES.skills s ON j.employee_id = s.EMPLOYEE_ID
    WHERE UPPER(COMPANY_NAME)=UPPER(?)
      AND end_date IS NULL
      AND start_date IS NOT NULL)
      file_format=(type=csv field_delimiter=\',\' compression=none ENCODING=UTF8
      field_optionally_enclosed_by= \'"\')
      header = true
      single = true
      OVERWRITE = TRUE
      max_file_size = 4900000000;';
                     
       EXECUTE IMMEDIATE :QUERY USING :Company_name;
       
       RETURN :QUERY;
    end;
    $$;

核心修改点：

1. 将WHERE子句中的UPPER(''Company_name'')替换为UPPER(?)，用?作为参数占位符
2. 执行SQL时添加USING :Company_name，将存储过程参数绑定到占位符上

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