How to Fix This Snowflake Sql Compilation Error

So I took your Stored proc, and I loaded it into my instance.

I altered it a tiny bit to use 4 different variable, and to also return those bits of SQL and made it note execute it ether.

CREATE OR REPLACE PROCEDURE REPLICA_RtHSNMaster(PARAM_STG_SUFFIX VARCHAR, TEMP_TBL_ID VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
  
var var_temp_table_name = `TEMP_RtHSNMaster_`+ TEMP_TBL_ID;
  
var sql_command1 = `CREATE OR REPLACE TEMPORARY TABLE ` + var_temp_table_name;
sql_command1 += ` AS SELECT * FROM RTHSNMASTER LIMIT 0 `;
       
//snowflake.execute ( {sqlText: sql_command1} );

var sql_command2 =`COPY INTO ` + var_temp_table_name;
sql_command2 += ` FROM @DATA_FILE_S3_STAGE` + PARAM_STG_SUFFIX;
sql_command2 +=` PATTERN =$$.*RtHSNMaster_.*csv$$ FILE_FORMAT =(FORMAT_NAME =$$CSV_FORMAT$$ FIELD_OPTIONALLY_ENCLOSED_BY = $$"$$ TIMESTAMP_FORMAT =$$YYYY-MM-DD HH:MI:SS.FF$$);`;
       
//snowflake.execute ({sqlText: sql_command2});
            
var sql_command3 =`DELETE FROM RTHSNMASTER using ` + var_temp_table_name;
sql_command3 += ` WHERE RTHSNMASTER.TENANTCODE=` + var_temp_table_name;
sql_command3 += `.TENANTCODE AND RTHSNMASTER.HSNID=` + var_temp_table_name;
sql_command3 += `.HSNID ; `;
       
//snowflake.execute ( {sqlText: sql_command3} );

var sql_command4 =`INSERT INTO RTHSNMASTER SELECT * FROM ` + var_temp_table_name;
        
//snowflake.execute ( {sqlText: sql_command4} );

var success = sql_command1 + `\r\n` + sql_command2 + `\r\n` + sql_command3 + `\r\n` + sql_command4 + `\r\n`; 

return  success;
';

Then I ran it:

call REPLICA_RTHSNMASTER ('A', 'B');

which gave this SQL:

CREATE OR REPLACE TEMPORARY TABLE TEMP_RtHSNMaster_B AS SELECT * FROM RTHSNMASTER LIMIT 0;  

COPY INTO TEMP_RtHSNMaster_B FROM @DATA_FILE_S3_STAGEA PATTERN =$$.*RtHSNMaster_.*csv$$ FILE_FORMAT =(FORMAT_NAME =$$CSV_FORMAT$$ FIELD_OPTIONALLY_ENCLOSED_BY = $$"$$ TIMESTAMP_FORMAT =$$YYYY-MM-DD HH:MI:SS.FF$$); 

DELETE FROM RTHSNMASTER using TEMP_RtHSNMaster_B WHERE RTHSNMASTER.TENANTCODE=TEMP_RtHSNMaster_B.TENANTCODE AND RTHSNMASTER.HSNID=TEMP_RtHSNMaster_B.HSNID ;  

INSERT INTO RTHSNMASTER SELECT * FROM TEMP_RtHSNMaster_B 

I then create some objects so each line would run:

CREATE TABLE RTHSNMASTER(TENANTCODE int, hsnid int, extra_value int);
CREATE STAGE DATA_FILE_S3_STAGEA;
CREATE FILE FORMAT CSV_FORMAT;

and all four lines ran, albeit I had no data in my empty stage.

As an aside that DELETE/INSERT could just be a UPSERT via a MERGE command.

Anyways I thought the error message, might happen if a string with whitespace was passed in.

so I changed the first command to:

CREATE OR REPLACE TEMPORARY TABLE TEMP_RtHSNMaster_ 95 AS SELECT * FROM RTHSNMASTER LIMIT 0;  

and in SnowSight I get the error:

Syntax error: unexpected '95'. (line 42)

which not similar, but I went back into classic console and looking at the error there it is:

SQL compilation error: syntax error line 1 at position 52 unexpected '95'. syntax error line 1 at position 52 unexpected '95'.

which matches much better. and position 28 is where the second SQL puts the TEMP_TBL_ID so I am not sure why the create is passing and it is failing on the second line.

So to workaround this you ether want to trim you varaibles.

var var_temp_table_name = `TEMP_RtHSNMaster_`+ TEMP_TBL_ID.trim();

OR double quote them:

var var_temp_table_name = `"TEMP_RtHSNMaster_`+ TEMP_TBL_ID +`"`;

OR fix the formatting on the system creating the input.

Here is the trimmed version:

CREATE OR REPLACE PROCEDURE REPLICA_RtHSNMaster(PARAM_STG_SUFFIX VARCHAR, TEMP_TBL_ID VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
  
var var_temp_table_name = `TEMP_RtHSNMaster_`+ TEMP_TBL_ID.trim();
  
var sql_command1 = `CREATE OR REPLACE TEMPORARY TABLE ` + var_temp_table_name;
sql_command1 += ` AS SELECT * FROM RTHSNMASTER LIMIT 0 `;
       
snowflake.execute ( {sqlText: sql_command1} );

var sql_command2 =`COPY INTO ` + var_temp_table_name;
sql_command2 += ` FROM @DATA_FILE_S3_STAGE` + PARAM_STG_SUFFIX;
sql_command2 +=` PATTERN =$$.*RtHSNMaster_.*csv$$ FILE_FORMAT =(FORMAT_NAME =$$CSV_FORMAT$$ FIELD_OPTIONALLY_ENCLOSED_BY = $$"$$ TIMESTAMP_FORMAT =$$YYYY-MM-DD HH:MI:SS.FF$$);`;
       
snowflake.execute ({sqlText: sql_command2});
            
var sql_command3 =`DELETE FROM RTHSNMASTER using ` + var_temp_table_name;
sql_command3 += ` WHERE RTHSNMASTER.TENANTCODE=` + var_temp_table_name;
sql_command3 += `.TENANTCODE AND RTHSNMASTER.HSNID=` + var_temp_table_name;
sql_command3 += `.HSNID ; `;
       
snowflake.execute ( {sqlText: sql_command3} );

var sql_command4 =`INSERT INTO RTHSNMASTER SELECT * FROM ` + var_temp_table_name;
        
snowflake.execute ( {sqlText: sql_command4} );

var success = sql_command1 + `\r\n` + sql_command2 + `\r\n` + sql_command3 + `\r\n` + sql_command4 + `\r\n`; 

return  success;
';

call REPLICA_RTHSNMASTER ('A', ' 95');

CREATE OR REPLACE TEMPORARY TABLE TEMP_RtHSNMaster_95 AS SELECT * FROM RTHSNMASTER LIMIT 0  

COPY INTO TEMP_RtHSNMaster_95 FROM @DATA_FILE_S3_STAGEA PATTERN =$$.*RtHSNMaster_.*csv$$ FILE_FORMAT =(FORMAT_NAME =$$CSV_FORMAT$$ FIELD_OPTIONALLY_ENCLOSED_BY = $$"$$ TIMESTAMP_FORMAT =$$YYYY-MM-DD HH:MI:SS.FF$$); 

DELETE FROM RTHSNMASTER using TEMP_RtHSNMaster_95 WHERE RTHSNMASTER.TENANTCODE=TEMP_RtHSNMaster_95.TENANTCODE AND RTHSNMASTER.HSNID=TEMP_RtHSNMaster_95.HSNID ;  

INSERT INTO RTHSNMASTER SELECT * FROM TEMP_RtHSNMaster_95