When creating stored procedure in Snowflake is there a way to return same output as a query, executed within it?

user_10601285 0
Viewed 428

I created a stored procedure that sets the certain variable value and executes merge using this variable. Currently, it just returns a hardcoded message "Successfully executed.". Is it possible to return the same result as the original merge query returns, like

number of rows inserted

number of rows updated

?

    CREATE OR REPLACE PROCEDURE ALERTS_MERGE_PROCEDURE ()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
    $$
    var sql_command = '
    MERGE INTO tablename
..
WHEN MATCHED THEN
UPDATE SET ...
WHEN NOT MATCHED THEN
INSERT ...
);
    '
    snowflake.execute(
        {
        sqlText: sql_command
        });
    return "Successfully executed.";
    $$;
snowflake-cloud-data-platform stored-procedures sql
2 Answers

You can iterate over the columns of the first row of the returned object from the execution:

create or replace temp table tablename as
select 1::int  id, 'a'::string tx;

create or replace temp table tablesource as
select 1::int  id, 'b'::string tx 
union select 2, 'c';

CREATE OR REPLACE PROCEDURE ALERTS_MERGE_PROCEDURE ()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    var sql_command = `
    merge into tablename a
    using tablesource b
    on a.id = b.id 
    when matched then update set tx=b.tx
    when not matched then insert (id, tx) values (b.id, b.tx);
    `;
    var x = snowflake.execute({sqlText: sql_command});
    x.next();
    var result = '';
    for (i=1; i<=x.getColumnCount(); i++) {
        result += x.getColumnName(i) + ': ' + x.getColumnValue(i) + '\n'; 
    }
    return result;
$$;
    
call alerts_merge_procedure();

Returns:

number of rows inserted: 1
number of rows updated: 1
user_1324380
user_136583990
Related