update a column inside a stored procedure

Question

I have two snowflake tables as below

TEST1

create OR REPLACE table TEST1 (
  id varchar(100), 
  name varchar(100),
  org VARCHAR(64)
);    

INSERT INTO TEST1 values (100, 'ABC', null);
INSERT INTO TEST1 values (200, 'XYZ', null);
INSERT INTO TEST1 values (300, 'VBN', null);

CONTROL

create OR REPLACE table CONTROL (
  KEY_COLUMNS VARCHAR,
  TABLE_NAME VARCHAR,
  org VARCHAR
);    

INSERT INTO CONTROL values ('id,name, address','TEST1','Z');
INSERT INTO CONTROL values ('id,name, address','TEST2','T');

I have created a stored procedure which updates column 'org' in TEST1 table with the values from CONTROL table (column org) where table name is TEST1.

CREATE OR REPLACE PROCEDURE TEST(source_tbl VARCHAR) 
    RETURNS string
    LANGUAGE SQL
    AS
    $$  
    DECLARE
           query1 STRING;
     BEGIN
           QUERY1 := 'update TEST1  set ORG = (select org from CONTROL WHERE TABLE_NAME = ''source_tbl'');';

           EXECUTE IMMEDIATE :QUERY1;

           RETURN :QUERY1;
      END;
      $$;
call TEST((select TABLE_NAME from CONTROL WHERE TABLE_NAME = 'TEST1'));

expected output

My output

Answer 1

The parameter should be bound variable instead of passing as the string constant ''source_tbl'':

CREATE OR REPLACE PROCEDURE TEST(source_tbl VARCHAR) 
RETURNS string
LANGUAGE SQL
AS
$$  
DECLARE
   query1 STRING;
BEGIN
  QUERY1 := 'update TEST1 set ORG = (select org from CONTROL WHERE TABLE_NAME = ?);';

  EXECUTE IMMEDIATE :QUERY1 USING (SOURCE_TBL);

  RETURN :QUERY1;
END;
$$

Related: EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE '<string_literal>'
 [ USING (bind_variable_1 [, bind_variable_2 ...] ) ] ;