Uncaught exception of type 'STATEMENT_ERROR' - Not sure how to fix this issue

Question

create or replace procedure sp_to_test() 
  returns varchar
  language sql
  as
  $$

declare

var_dn VARCHAR2(6200);
tmp_str integer;
prc_nm varchar2(100);

begin
     
  select count(1) into tmp_str from information_schema.tables where table_name ='TMP_TBL_TO_TEST';
         
    IF (tmp_str = 1) then 
         var_dn:='Drop Table TMP_TBL_TO_TEST';
                 execute immediate var_dn;
    END IF;
                 
        INSERT INTO log_tbl (sql_log_key,  rsi_var, txt_val, v_date)
        VALUES (seq_val.nextval,  'sp_to_test', var_dn, SYSDATE());
        commit;
    
        
    var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
    INSERT INTO log_tbl (sql_log_key,  rsi_var, txt_val, v_date)
    VALUES (seq_val.nextval,  'sp_to_test', var_dn, SYSDATE());
    commit;

    execute immediate var_dn;


end ;
$$
;

When I run this procedure in Snowflake, I'm getting this error - Uncaught exception of type 'STATEMENT_ERROR' on line 23 at position 8 : SQL compilation error: error line 2 at position 77 invalid identifier 'var_dn'

Not sure what I'm doing wrong. Kindly guide in fixing the issue.

Answer 1

First two statemensts are could be rewritten:

select count(1) into tmp_str 
from information_schema.tables 
where table_name ='TMP_TBL_TO_TEST';
         
IF (tmp_str = 1) then 
         var_dn:='Drop Table TMP_TBL_TO_TEST';
         execute immediate var_dn;
END IF;

as simple:

DROP TABLE IF NOT EXISTS TMP_TBL_TO_TEST;

Second:

var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
INSERT INTO log_tbl (sql_log_key,  rsi_var, txt_val, v_date)
VALUES (seq_val.nextval,  'sp_to_test', var_dn, SYSDATE());

should be(variable accessed via SQL statement should be prefixed with :):

var_dn:='Create Table TMP_TBL_TO_TEST AS Select * From ORG_TBL';
INSERT INTO log_tbl (sql_log_key,  rsi_var, txt_val, v_date)
VALUES (seq_val.nextval,  'sp_to_test', :var_dn, SYSDATE());