Snowflake JS存储过程拼接带双引号SQL实现列名转大写报错如何解决

你遇到的语法报错是转义层级冲突导致的：你在JavaScript中用双引号包裹整个SQL字符串，同时又要在SQL的单引号字符串片段内插入双引号，两层转义规则混在一起导致最终生成的SQL语法不符合Snowflake要求。

这里提供2种可直接落地的方案，优先推荐第一种，完全规避转义冲突问题：

方案1：用SQL内置CHAR函数生成双引号（最稳妥） #

直接在SQL层面用CHAR(34)输出双引号，JavaScript代码中完全不需要写双引号，从根源避免转义混乱，同时配合JS模板字符串简化拼接逻辑：

var table_control = `
SELECT CONCAT(
  'ALTER TABLE ', CHAR(34), TABLE_SCHEMA, CHAR(34), '.', CHAR(34), TABLE_NAME, CHAR(34),
  ' RENAME COLUMN ', CHAR(34), COLUMN_NAME, CHAR(34),
  ' to ', CHAR(34), UPPER(COLUMN_NAME), CHAR(34), ';'
) AS COL1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '${CURRENT_SCHEMA}'
AND TABLE_NAME = '${CURRENT_TABLE}'
AND COLUMN_NAME != UPPER(COLUMN_NAME)
`;

方案2：修正转义写法 #

如果你坚持直接写双引号，注意JS字符串中双引号需要加\转义，且确保SQL单引号片段的闭合规则正确，修正后的写法如下：

var table_control = " SELECT CONCAT('ALTER TABLE \"', TABLE_SCHEMA,'\".\"' ,TABLE_NAME, '\" RENAME COLUMN \"', COLUMN_NAME, '\" to \"', UPPER(COLUMN_NAME), '\";') AS COL1 " 
    table_control += "  FROM INFORMATION_SCHEMA.COLUMNS "
    table_control += " WHERE TABLE_SCHEMA = '" + CURRENT_SCHEMA + "'"
    table_control += "   AND TABLE_NAME   = '" + CURRENT_TABLE + "'"
    table_control += "   AND COLUMN_NAME != UPPER(COLUMN_NAME) " ;

CREATE OR REPLACE PROCEDURE CONVERT_TBL_COLUMNS_UPPERCASE(CURRENT_SCHEMA VARCHAR, CURRENT_TABLE VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var get_alter_sql = `
SELECT CONCAT(
  'ALTER TABLE ', CHAR(34), TABLE_SCHEMA, CHAR(34), '.', CHAR(34), TABLE_NAME, CHAR(34),
  ' RENAME COLUMN ', CHAR(34), COLUMN_NAME, CHAR(34),
  ' to ', CHAR(34), UPPER(COLUMN_NAME), CHAR(34), ';'
) AS COL1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '${CURRENT_SCHEMA}'
AND TABLE_NAME = '${CURRENT_TABLE}'
AND COLUMN_NAME != UPPER(COLUMN_NAME)
`;

var stmt = snowflake.createStatement({sqlText: get_alter_sql});
var res = stmt.execute();
var modified_count = 0;

while(res.next()){
  var alter_sql = res.getColumnValue(1);
  snowflake.createStatement({sqlText: alter_sql}).execute();
  modified_count++;
}

return `已成功将 ${modified_count} 个列名转换为大写`;
$$;

-- 调用示例
-- CALL CONVERT_TBL_COLUMNS_UPPERCASE('SCHEMA_S', 'TBLNAME');

• 执行存储过程前，建议先单独执行生成ALTER语句的SELECT查询，确认修改范围无误后再执行批量修改
• 列名修改会影响依赖原小写列名的视图、存储过程、下游应用代码，修改前需做好兼容性校验
• 如果你的模式名/表名本身包含单引号，需要额外对输入参数做单引号转义处理，普通场景下上述代码可直接运行

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