Snowflake存储过程中创建临时表存储会话变量的语法问题

存在的问题 #

• 结果集游标未初始化：查询返回的ResultSet对象默认定位在第一行数据之前，必须先调用next()方法才能读取字段值
• 保留字未转义：DATABASE、SCHEMA属于Snowflake SQL保留关键字，作为自定义字段名使用时需要用双引号包裹
• INSERT语句语法错误：拼接字符串类型的数据库名、模式名时没有添加单引号包裹，会被SQL解析器识别为标识符而非字符串值；同时JavaScript中双引号包裹的字符串不支持直接跨行，会触发JS语法错误
• 缺少返回值：存储过程声明了RETURNS VARCHAR类型返回值，但代码中没有对应return语句，不符合Snowflake存储过程语法规范

修复后的完整代码 #

CREATE OR REPLACE PROCEDURE GET_CONTEXT()
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  COMMENT = 'Saves current database and current schema in an array'
  EXECUTE AS CALLER
  AS
  $$
    var arr_context = [];
    v_sqlCode = "SELECT CURRENT_DATABASE(), CURRENT_SCHEMA()";
   
    try{
        var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
        var sqlRS = sqlStmt.execute();
        // 移动游标到第一行数据
        sqlRS.next();
    }catch(err){
        errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
        errMessage += "\n  Message: " + err.message;
        errMessage += "\nStack Trace:\n" + err.stackTraceTxt;  
        throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
       }
     
    var v_sqlCode = 'CREATE TEMPORARY TABLE TEMP_HOLDER ("DATABASE" VARCHAR(16777216), "SCHEMA" VARCHAR(16777216))';
   
        try{
        var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
        var sqlRS2 = sqlStmt.execute();
    }catch(err){
        errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
        errMessage += "\n  Message: " + err.message;
        errMessage += "\nStack Trace:\n" + err.stackTraceTxt;  
        throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
       }
       
    // 使用绑定变量避免引号拼接问题，规避SQL注入风险
    v_sqlCode = `INSERT INTO TEMP_HOLDER("DATABASE", "SCHEMA") VALUES(?, ?)`;
    try{
        var sqlStmt = snowflake.createStatement({
            sqlText :  v_sqlCode,
            binds: [sqlRS.getColumnValue(1), sqlRS.getColumnValue(2)]
        });
        var sqlRS = sqlStmt.execute();
    }catch(err){
        errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
        errMessage += "\n  Message: " + err.message;
        errMessage += "\nStack Trace:\n" + err.stackTraceTxt;  
        throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
    }
    // 补充返回值匹配存储过程声明
    return '上下文信息已成功保存到TEMP_HOLDER临时表';
  $$;
   
CALL GET_CONTEXT();

内容的提问来源于stack exchange，提问作者PayrollPaul