Capture Success Message in Snowflake Stored Procedure

Question

I have several Snowflake Stored Procedures and when they are successful, it has the custom message that I created displayed, I would rather be displaying the same message that Snowflake shows in the Query Results window.
For Example, I execute a Stored Proc with the COPY TO statements. I would like the Successful execution of this Stored Proc to show the number of Rows successfully Exported. Can they success information be captured and displayed as easily as the Error messages are?

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-04-01T17:58:32

Yes, you can do this using JavaScript stored procedures. When Snowflake runs a query that returns only a status message, it returns it as a table with a single column "status" and a single row with the status. You can return that value. You may want to consider what happens if there's a SQL error: handle it locally in the SP or throw the error so the calling session knows there was an error. Either way the status return will show an error message if there is one.

Here's a sample using JavaScript SP. It also has some helper functions that I use routinely to execute single value queries and non-query statements just looking for a return value:

create or replace procedure SampleSP()
returns string
language javascript
as
$$
    try{
        return ExecuteNonQuery("create table MY_NATION_TABLE as select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;");
    }
    catch(err){
        return err;
    }
// ----------------------------------------------------------------------------------
// Main function above; helper functions below

    function ExecuteNonQuery(queryString) {
        return ExecuteSingleValueQuery("status", queryString)
    }

    function ExecuteSingleValueQuery(columnName, queryString) {
        var out;
        cmd1 = {sqlText: queryString};
        stmt = snowflake.createStatement(cmd1);
        var rs;
        try{
            rs = stmt.execute();
            rs.next();
            return rs.getColumnValue(columnName);
        }
        catch(err) {
            if (err.message.substring(0, 18) == "ResultSet is empty"){
                throw "ERROR: No rows returned in query.";
            } else {
                throw "ERROR: " + err.message.replace(/\n/g, " ");
            } 
        }
        return out;
    }
$$;

-- Run this twice to see the effect of an error. You can remove the try block
-- in the main function of the SP to generate a SQL error instead of just 
-- returning a string with the error text

call SampleSP();

Capture Success Message in Snowflake Stored Procedure

1 Answers