Merge statement and stored procedure

Question

I am trying to write a stored procedure with a Merge statement. It looks something like this:

create or replace procedure database.events.insert_groupss()
returns string
LANGUAGE JAVASCRIPT
as
$$
var sql_cmd = 'Merge into database.events.groups et using
        (select lower(f.VALUE:id::string) as id,
                min(f.VALUE:time_timestamp::timestamp) as event_timestamp,
                f.VALUE:service_string::string as service,
                f.VALUE:title_string::string as title,
                min(X.VALUE:created_at::timestamp) as title_ts ,
                X.VALUE:group::string as group
         from \'@database.sources.s3stage/version=3/stream=live/year=2019/month=12/\'
         (file_format => \'oak.public.JSON_FORMAT\' ), lateral flatten (input => $1:group_events, RECURSIVE => TRUE) f
         , LATERAL FLATTEN(parse_json(replace(replace(replace(replace(f.value:groups ,\'"[\', \'[\'),\'=>\',\':\'),\'\\\\\',\'\'),\']"\',\']\'))) x
          where id is not null and group is not null
          group by id,group,service,title)a
         on et.id=a.id and et.group = a.group
         when not matched then Insert (id , event_timestamp , service , title , title_ts ,group  )
                               values (a.id , a.event_timestamp , a.service , a.title , a.title_ts ,a.group  );';
try {

    snowflake.execute ({sqlText: sql_cmd});

    return "Succeeded";

    }

catch (err) {

    return "Failed: " + err;  // Return a success/error indicator.

    }
$$;

The stored procedure was created, but if I run it, I am getting some syntax error. The escaping characters were escaped and the query was getting executed when I run it manually (with using the stored procedure).

Here is the error I am getting

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in INSERT_GROUPS at 'var sql_cmd = 'Merge into database.events.groups et using' position 14

Answer 1

The problem is sql_cmd is in muti line so you need to concatenate or it should be in single line.

create or replace procedure  database.events.insert_groupss()
returns string
LANGUAGE JAVASCRIPT
as
$$
var sql_cmd  = 'Merge into database.events.groups et using'
    sql_cmd += ' (select lower(f.VALUE:id::string) as id,'
    sql_cmd += ' min(f.VALUE:time_timestamp::timestamp) as event_timestamp,'
    sql_cmd += ' f.VALUE:service_string::string as service,'
    sql_cmd += ' f.VALUE:title_string::string as title,'
    sql_cmd += ' min(X.VALUE:created_at::timestamp) as title_ts ,'
    sql_cmd += ' X.VALUE:group::string as group'
    sql_cmd += ' from \'@database.sources.s3stage/version=3/stream=live/year=2019/month=12/\''
    sql_cmd += ' (file_format => \'oak.public.JSON_FORMAT\' ), lateral flatten (input => $1:group_events, RECURSIVE => TRUE) f'
    sql_cmd += ' , LATERAL FLATTEN(parse_json(replace(replace(replace(replace(f.value:groups ,\'"[\', \'[\'),\'=>\',\':\'),\'\\\\\',\'\'),\']"\',\']\'))) x'
    sql_cmd += ' where id is not null and group is not null'
    sql_cmd += ' group by id,group,service,title)a'
    sql_cmd += ' on et.id=a.id and et.group = a.group'
    sql_cmd += ' when not matched then Insert (id , event_timestamp , service , title , title_ts ,group  )'
    sql_cmd += ' values (a.id , a.event_timestamp , a.service , a.title , a.title_ts ,a.group  );";'
try {

    snowflake.execute ({sqlText: sql_cmd});

    return "Succeeded";

    }

catch (err) {

    return "Failed: " + err;  // Return a success/error indicator.

    }
$$;

Answer 2

Issue is in try clause , incorrect SQL variable name. Replace with below

snowflake.execute ({sqlText: sql_cmd})