CODEWITHSUNDEEP
stored-proceduressnowflake-cloud-data-platform
sh1704
sh1704

Reputation: 69

snowflake stored procedure is throwing unexpected Token error

I have written down a procedure by following the snowflake documentation to generate a list of 'SET' statement and execute them in Snowflake. I'm getting Unexpected identifier error while calling the procedure. could somebody please help me out here.

create or replace procedure SET_ENV()
    returns string
    language JavaScript
    as
    $$
    MASTER_QUERY = {sqlText: SELECT ('SET '||TABLE_NAME||'= 
    CONCAT($Database_pre,'||'''.'''||',$Schema_pre,'||'''.'''||','''||TABLE_NAME||''');') AS 
 QUERY
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 AND( TABLE_NAME NOT LIKE 'TMP_%' AND TABLE_NAME NOT LIKE '%WRK_%')};
    STATEMENT = snowflake.createStatement(MASTER_QUERY);

  rs = STATEMENT.execute();



  var s = '';
  while (rs.next())  {
  
      EXECUTION_QUERY = {sqlText: rs.getColumnValue("QUERY")};
      stmtEx = snowflake.createStatement(EXECUTION_QUERY);
      stmtEx.execute();
      s += rs.getColumnValue(1) + "\n";
      }
       
  return s;
  
  $$

;

 CALL SET_ENV()

The error i'm getting is as follows.

JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in SET_ENV at 'MASTER_QUERY = {sqlText: 'SELECT ('SET '||TABLE_NAME||'= ' position 35

Upvotes: 1

Views: 1439

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

I'm able to run it after I add backtick (`) characters to enclose the SQL command:

create or replace procedure SET_ENV()
    returns string
    language JavaScript
    execute as CALLER
    as
    $$
    MASTER_QUERY = {sqlText: `SELECT ('SET '||TABLE_NAME||'= 
    CONCAT($Database_pre,'||'''.'''||',$Schema_pre,'||'''.'''||','''||TABLE_NAME||''');') AS 
 QUERY  
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 AND( TABLE_NAME NOT LIKE 'TMP_%' AND TABLE_NAME NOT LIKE '%WRK_%')` };
    STATEMENT = snowflake.createStatement(MASTER_QUERY);

  rs = STATEMENT.execute();


  var s = '';
  while (rs.next())  {
  
      EXECUTION_QUERY = {sqlText: rs.getColumnValue("QUERY")};
      stmtEx = snowflake.createStatement(EXECUTION_QUERY);
      stmtEx.execute();
      s += rs.getColumnValue(1) + "\n";
      }
       
  return s;
  
  $$;

set DATABASE_PRE='DBPRE';
set Schema_pre = 'SCHPRE';

call SET_ENV();

Important: You should also define the procedure as "execute as CALLER" to be able to access session variables. I suggest you define arguments instead of accessing session variables.

Upvotes: 4

Related Questions