Snowflake SQL compilation error: error line 1 at position 55 N"'TEST'"-CodePudding

Just started using SnowFlake and experiencing issues making a basic snowflake insert builder procedure where if the insert parameter on call is process with a "''" it will error. In the example below. SQL compilation error: error line 1 at position 55 N"'TEST'"

The procedure:

CREATE OR REPLACE PROCEDURE "INSERTTABLECOLUMNS"("TABLENAME" VARCHAR(250), "INSERTCOLUMNS" VARCHAR(250), "INSERTVALUES" VARCHAR(250))
    RETURNS VARCHAR()
    LANGUAGE JAVASCRIPT
    AS
    $$
    var command = "INSERT INTO " TABLENAME " (" INSERTCOLUMNS ") VALUES (" INSERTVALUES ")";  
    var cmd1_dict = {sqlText: command};  
    var stmt = snowflake.createStatement(cmd1_dict);  
    var rs = stmt.execute();  
    return "Works";  
    $$;

The Call

call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME',"'TEST'")
call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME,COLUMN2',"'TEST',2")

CodePudding user response：

the double quotes are mostly used for signifying object names when you have abnormal characters in your table/column names, for example.

I think what you want is to escape your ' characters. You can do that either with a double apostrophe: '' or a backslash: \' .

call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME','''TEST''')
call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME,COLUMN2','''TEST'',2')

call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME','\'TEST\'')
call DEMO.PUBLIC.INSERTTABLECOLUMNS('TESTBEGIN','NAME,COLUMN2','\'TEST\',2')