CODEWITHSUNDEEP
snowflake-cloud-data-platform
Krunchworks
Krunchworks

Reputation: 1

Snowflake stored procedure system$send_email

I am trying to create a stored procedure to trigger an email alert using snowflake stored procedure system$send_email. The ask is to return some variable in the body of the send_email like displaying the count of users created by account admin.

Here is my code:

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','[email protected]','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;

$$

;

I get this error:

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

but when I remove +cnt+ , email gets triggered.

Please help

Upvotes: 0

Views: 1532

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10059

You just need to change this part:

  var proc = `call system$send_email('my_email_int','[email protected]','Admin user details',

  'Total users created by ACCOUNTADMIN are and user details are' +cnt+ '');`

as this:

  var proc = `call system$send_email('my_email_int','[email protected]','Admin user details',

  'Total users created by ACCOUNTADMIN are and user details are ` + cnt + `');`

Upvotes: 1

Related Questions