雪花存储过程正在引发意外的Token错误

Question

我已经按照雪花文档写下了一个过程，生成一个"SET"语句列表，并在雪花中执行它们。调用过程时出现意外标识符错误。有人能帮我一下吗。

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() 

我得到的错误如下。

JavaScript编译错误：未捕获的语法错误：SET_ENV中位于'MASTER_QUERY=｛sqlText:'SELECT('SET'||TABLE_NAME|'='位置35 处的意外标识符

Answer 1

我可以在添加反勾号(`(字符以包含SQL命令后运行它：

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();

重要：您还应该将过程定义为"；执行为CALLER"；以便能够访问会话变量。我建议您定义参数，而不是访问会话变量。