How to escape single quote while dynamically creating sql in a snowflake stored procedure?

Question

 CREATE OR REPLACE PROCEDURE DEPARTMENT_STORED_PROC(table_name VARCHAR)
    returns variant not null
    
    language javascript
    as
    $$    
    
    var sql_cmd_ingest = "copy into DEPARTMENT.CLASSES." + TABLE_NAME + "from '@DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/" + TABLE_NAME + "';";
        snowflake.execute({sqlText: sql_cmd_ingest} );
$$

The above is just a code snippet. I am getting error for single quote before '@DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/"

Execution error in store procedure DEPARTMENT_STORED_PROC: SQL compilation error: syntax error line 1 at position 70 unexpected ''@DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/CLASSES_TABLE''. At Snowflake.execute, line 11 position 10

Answer 1

You need a space after TABLE_NAME and before "from

var sql_cmd_ingest = "copy into DEPARTMENT.CLASSES." + TABLE_NAME + " from '@DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/" + TABLE_NAME + "';";

Also I highly recommend using JavaScript replacement variables when building SQL statements inside stored procedures. You can use three different types of quotes to define strings in JavaScript, double " single ' and back tick `.

If you use the back tick to start and end your strings, you can then use any JavaScript variable inside that string without terminating the string and concatenating with +. For example:

var sql_cmd_ingest = `copy into DEPARTMENT.CLASSES.${TABLE_NAME} from '@DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/${TABLE_NAME}';`;

It makes the SQL much more readable, especially when the SQL is long and multi-line.

Also, the stage name should not be in quotes in the SQL. You should remove them:

var sql_cmd_ingest = `copy into DEPARTMENT.CLASSES.${TABLE_NAME} from 
 @DEPARTMENT.CLASSES.CLASSES_PREPROD_STAGE/${TABLE_NAME};`;