Is there a simple way to retrieve a scalar result from a Snowflake stored procedure

Question

Is there a simple way to retrieve a scalar result from a Snowflake stored procedure within another stored procedure?

I would like to use one stored procedure to generate a complex SQL statement and fire that SQL statement into another procedure.

CREATE OR REPLACE PROCEDURE GENERATE_SQL( )
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS 
$$
return "SELECT 'Hello world'";
$$
;

Having the SQL generation in a separate procedure would allow me to more easily maintain/unit test that bit.

Answer 1

Not directly.

You can call the stored procedure inside another stored procedure; the JavaScript in the outer stored procedure can retrieve and store the output of the inner stored procedure. Remember, however, that the outer stored procedure (and each inner stored procedure) is still unable to return more than one value to its caller.

You can call the stored procedure and then call the RESULT_SCAN function and pass it the statement ID generated for the stored procedure.

You can store a result set in a temporary table or permanent table, and use that table after returning from the stored procedure call.

If the volume of data is not too large, you can store multiple rows and multiple columns in a VARIANT (for example, as a JSON value) and return that VARIANT.

Answer 2

I'd like to add to MMV's answer. You can read the results of the first SP without calling the RESULT_SCAN. When you call a Stored Procedure, you get a single-row result set that has a single column with the same name as a stored procedure you called. So you can just call the SP like a query and fetch the value in that column like this:

create or replace procedure second_sp()
returns string
language javascript
as
$$

    return "Hello world."

$$;

create or replace procedure first_sp()
returns string
language javascript
as
$$

  // Use the name of the SP called as the column name for the return:
  return ExecuteSingleValueQuery("SECOND_SP", "call second_sp()");

//  Helper function to read a single-value query

    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;
    }
$$;

call second_sp();
call first_sp();