Snowflake存储过程调用system$send_email传变量报错求助

我尝试创建Snowflake存储过程，调用system$send_email触发邮件告警，需求是在邮件正文中展示ACCOUNTADMIN创建的用户数量等变量。以下是我的代码：

create or replace procedure users_type_notify()
returns string
language javascript
execute as caller
as
$$
var qry = ` show users `;
var qry_rslt = snowflake.execute({sqlText:qry});
var qry_id = qry_rslt.getQueryId();
var qry2 = ` select "name" , "owner" from table(result_scan('${qry_id}')) `;
rs = snowflake.execute({sqlText:qry2});

var admin_owner_nm = " ";
var aad_owner_nm = " ";
var eowner_nm = " ";
var final_list = " ";

var a = 0;
var e = 0;
var p = 0;
var cnt = 0;
var pcnt = 0;
var ecnt = 0;

while (rs.next())
{
  if (rs.getColumnValue(2) == "ACCOUNTADMIN")
  {
    if (a++ > 0) admin_owner_nm += ",";
    admin_owner_nm += rs.getColumnValue(1);
    cnt = cnt + 1;
  }
  else if (rs.getColumnValue(2) =="AAD_PROVISIONER")
  {
    if (p++ > 0) aad_owner_nm += ",";
    aad_owner_nm += rs.getColumnValue(1);
    pcnt = pcnt + 1;
  }
}

if (cnt > 0)
{
  var proc = `call system$send_email('my_email_int','raja.pathak@icloud.com','Admin user details',
  'Total users created by ACCOUNTADMIN are and user details are' +cnt+ '');`
  var stmt = snowflake.createStatement({sqlText:proc});
  var result = stmt.execute();
}

return cnt;
$$;

执行时出现错误：

Execution error in stored procedure USERS_TYPE_NOTIFY: SQL compilation error: error line 2 at position 69 invalid identifier 'CNT' At Statement.execute, line 44 position 22

移除代码中的+cnt+后，邮件可正常触发，需要解决该问题。

错误原因 #

你在JavaScript模板字符串中错误使用了普通字符串的变量拼接方式（+cnt+），但模板字符串本身支持通过${变量名}直接插入变量。当你用+cnt+时，生成的SQL语句会把CNT当作Snowflake的标识符（列名或系统变量），而非JavaScript变量的实际值，导致SQL编译失败。

修正后的代码 #

create or replace procedure users_type_notify()
returns string
language javascript
execute as caller
as
$$
var qry = `show users`;
var qry_rslt = snowflake.execute({sqlText: qry});
var qry_id = qry_rslt.getQueryId();
var qry2 = `select "name", "owner" from table(result_scan('${qry_id}'))`;
rs = snowflake.execute({sqlText: qry2});

var admin_owner_nm = "";
var aad_owner_nm = "";
var eowner_nm = "";
var final_list = "";

var a = 0;
var e = 0;
var p = 0;
var cnt = 0;
var pcnt = 0;
var ecnt = 0;

while (rs.next()) {
    if (rs.getColumnValue(2) === "ACCOUNTADMIN") {
        if (a++ > 0) admin_owner_nm += ",";
        admin_owner_nm += rs.getColumnValue(1);
        cnt++;
    } else if (rs.getColumnValue(2) === "AAD_PROVISIONER") {
        if (p++ > 0) aad_owner_nm += ",";
        aad_owner_nm += rs.getColumnValue(1);
        pcnt++;
    }
}

if (cnt > 0) {
    // 用模板字符串直接插入JavaScript变量
    var emailContent = `Total users created by ACCOUNTADMIN are ${cnt}. User details: ${admin_owner_nm}`;
    var proc = `call system$send_email('my_email_int','raja.pathak@icloud.com','Admin user details','${emailContent}')`;
    var stmt = snowflake.createStatement({sqlText: proc});
    stmt.execute();
}

// 确保返回值符合string类型要求
return cnt.toString();
$$;

关键修改说明 #

• 使用${cnt}和${admin_owner_nm}直接在模板字符串中插入JavaScript变量，生成正确的邮件内容。
• 将cnt = cnt + 1简化为cnt++，代码更简洁。
• 返回cnt.toString()，匹配存储过程声明的string返回类型。
• 初始化admin_owner_nm为空字符串，避免邮件内容开头出现多余空格。

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