How to pass parameter in snowflake stored procedure

Question

I am trying to pass Company_name as parameter, it being called twice in the SQL Query. First it is being replaced by value but for second instance value is getting passed. UPPER(COMPANY_NAME)=UPPER(''Company_name'') .

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;
    $$;

Answer 1

The reason for this problem is that you (correctly) escaped the parameter, but (incorrectly) hard-coded Company_name as a string value rather than concatenating the value of the parameter that has this name. Try this:

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;
    $$;