Snowflake JavaScript存储过程变量赋值生成文件名报错求助

错误原因 #

你遇到的语法错误核心是直接将snowflake.createStatement生成的语句对象拼接到SQL字符串中，而非提取查询结果里的实际FILE_NAME_POSTFIX值。语句对象本身不是字符串，拼接后会让生成的COPY INTO语句语法完全混乱，触发编译错误。

修正后的完整代码 #

CREATE or replace procedure  "SP_MONTHLY_FINANCE_REPORTS"()
RETURNS VARCHAR(10000)
language javascript
as
$$
var ReturnVal = 'REPORT PROC EXECUTION Complete';

// 创建临时表获取日期后缀
var TMP_DATES_crt = `create or replace temporary table TMP_DATES AS 
select *
, case when len(FISCAL_PERIOD) = 1 then concat( to_varchar(FISCAL_YEAR) ,'0',to_varchar( FISCAL_PERIOD) )
when len(FISCAL_PERIOD) > 1 then concat( to_varchar(FISCAL_YEAR) ,to_varchar( FISCAL_PERIOD) ) 
end as File_Name_PostFix
,concat(to_varchar(FISCAL_YEAR),' - ',to_varchar( FISCAL_PERIOD) ) as File_FiscalYear_FiscalPeriod_concat
from (
select a.FISCAL_YEAR,a.FISCAL_PERIOD ,b.MONTH_END_DATE
from (
select FISCAL_YEAR , FISCAL_PERIOD,BEGNNING_DATE_PV
from  Calendar
         where FISCAL_PERIOD >= 1 and FISCAL_YEAR = year( CURRENT_date() )
         and FISCAL_PERIOD = ( month( CURRENT_date() ) - 1 ) 
           and FISCAL_PERIOD_WEEK = 1 and FISCAL_PERIOD_DAY = 1
) as a
FULL OUTER join (
select FISCAL_YEAR , FISCAL_PERIOD, (BEGNNING_DATE_PV -1 ) as MONTH_END_DATE
from  Calendar
         where FISCAL_PERIOD >= 1 and FISCAL_YEAR = year( CURRENT_date() ) 
         and FISCAL_PERIOD = ( month( CURRENT_date() ) ) 
           and FISCAL_PERIOD_WEEK = 1 and FISCAL_PERIOD_DAY = 1
) as b on a.FISCAL_YEAR = b.FISCAL_YEAR
) as c ;`;
var TMP_DATES_crt_stmt = snowflake.createStatement({sqlText: TMP_DATES_crt});
var TMP_DATES_crt_res= TMP_DATES_crt_stmt.execute();

// 提取FILE_NAME_POSTFIX值
var dateStmt = snowflake.createStatement({sqlText: `select FILE_NAME_POSTFIX from TMP_DATES`});
var dateResult = dateStmt.execute();
var fileNamePostfix = '';
// 移动到结果行并获取值
if (dateResult.next()) {
    fileNamePostfix = dateResult.getColumnValue('FILE_NAME_POSTFIX');
} else {
    throw new Error("未查询到有效的日期后缀数据");
}

// 动态构建COPY INTO语句
var File_1_crt = `copy into @StagingAzure/FINANCE/TEST_Summary_${fileNamePostfix}_.gz
from ( Select 1
FROM PERIOD_SUM
 )
SINGLE=true file_format=(type=csv null_if = ('NULL', 'null')
  empty_field_as_null=false) MAX_FILE_SIZE = 150000000 OVERWRITE=TRUE ;`; 
var File_1_crt_stmt = snowflake.createStatement({sqlText: File_1_crt});
var File_1_crt_res= File_1_crt_stmt.execute();  

ReturnVal = 'REPORTS EXECUTION Complete';
return ReturnVal;
$$

关键修改点 #

• 正确提取查询结果：执行查询后，通过dateResult.next()移动到结果行，再用getColumnValue()获取实际的FILE_NAME_POSTFIX字符串值
• 模板字符串拼接：改用ES6模板字符串（${变量}）拼接文件名，比字符串加法更清晰不易出错
• 增加异常判断：如果查询无结果，主动抛出错误，避免后续执行无效的COPY INTO语句

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