Snowflake Stored procedure with dynamic SQL statement Error

user_11593771 0
Viewed 119

I am testing following test sp , Proc is complaining about sql command

JavaScript compilation error: Uncaught SyntaxError: Unexpected string in TEST_PROC at ' var sqlCommand

What i would like to be done in this proc is, Run select on table and prepare alter statements and execute all statements.

CREATE OR REPLACE PROCEDURE test_proc()
RETURNS STRING
LANGUAGE javascript 
AS
$$
    
            var sqlCommand =  "select ''ALTER EXTERNAL TABLE''|| '' '' || SCHEMA_NAME ||''.'' || TABLE_NAME ||'' ''|| ''REFRESH'' ||'' ''''''|| LOCATION ||''''''''
                               from EXT_TABLE_CONGIG 
                               where TABLE_NAME =''TABLEXYZ'';"

            var stmt = snowflake.createStatement({ sqlText: sqlCommand } );
            
            stmt.execute();
            return 'success'
  

$$;```
snowflake-cloud-data-platform
2 Answers

You cannot define a multi-line string using double quotes in JavaScript. There's also a quote balance issue.

Using backquotes (backticks) allows multi-line strings and use of either single or double quotes without having to double them.

CREATE OR REPLACE PROCEDURE test_proc()
RETURNS STRING
LANGUAGE javascript 
AS
$$

var sqlCommand =  `select 'ALTER EXTERNAL TABLE' || ' ' || SCHEMA_NAME || '.' || TABLE_NAME || ' ' || 'REFRESH' || '''' || LOCATION || ''''
                               from EXT_TABLE_CONGIG 
                               where TABLE_NAME = 'TABLEXYZ';`

var stmt = snowflake.createStatement({ sqlText: sqlCommand } );

var rs = stmt.execute();

rs.next();

var sql = rs.getColumnValue(1);

stmt = snowflake.createStatement({ sqlText: sql });

stmt.execute();

return 'success';

$$;

call test_proc();
user_127563810

Below example is to demonstrate run dynamic SQL in procedure - Answer from @Greg addresses multi-line in java script already.

CREATE OR REPLACE PROCEDURE test_proc()
RETURNS STRING
LANGUAGE javascript 
AS
$$
  var sqlCommand =  "select 'alter table '||tname||' add (id number)' from t_name where tname in ('t1','t2','t3')";

  var stmt = snowflake.createStatement({ sqlText: sqlCommand } );
  
  var v_result = stmt.execute();
  while(v_result.next()) {
  var stmt1 = snowflake.createStatement({ sqlText: v_result.getColumnValue(1) } );
  stmt1.execute();
  var f_result = v_result.getColumnValue(1);
  };
  return 'success';
$$
;

Also refer -

Read through - "Here’s an example that retrieves a ResultSet and iterates through it:"

user_139153130
Related