Snowflake Javascript SP output as Table?

Question

I'm writing one SP where output is expected as table. But not able to get output as in table format, but receiving it as an object a single value or all rows in one column while using array as return type.

'''

create or replace table monthly_sales(empid int, amount int, month text)
as select * from values
(1, 10000, 'JAN'),
(1, 400, 'JAN'),
(2, 4500, 'JAN'),
(2, 35000, 'JAN'),
(1, 5000, 'FEB'),
(1, 3000, 'FEB'),
(2, 200, 'FEB'),
(2, 90500, 'FEB'),
(1, 6000, 'MAR'),
(1, 5000, 'MAR'),
(2, 2500, 'MAR'),
(2, 9500, 'MAR'),
(1, 8000, 'APR'),
(1, 10000, 'APR'),
(2, 800, 'APR'),
(2, 4500, 'APR'),
(2, 10000, 'MAY'),
(1, 800, 'MAY');

select * from MONTHLY_SALES;

create or replace procedure getRowCount(TABLENAME VARCHAR(1000)) returns variant not null language javascript as $$ // Dynamically compose the SQL statement to execute.

var sql_command = " SELECT * FROM "+TABLENAME+";"

// Prepare statement.
var stmt = snowflake.createStatement({sqlText: sql_command});

// Execute Statement try { var rs = stmt.execute();

return rs;

}catch(err){return "error "+err;} $$;

Call getRowCount('MONTHLY_SALES');

'''

Expected Output: enter image description here

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-04-21T13:58:09

Snowflake stored procedures can not have an output type of table. You have a few options. One option is writing a stored procedure that returns an array or JSON that you can flatten into a table. Note though, that you cannot use the return of a stored procedure directly. You'd have to first run the stored procedure, and as the very next statement executed in the session collect the output like this:

select * from  table(result_scan(last_query_id()));

Another option is writing a user defined table function (UDTF), which is the only function type that returns a table in Snowflake. Here's an example of a simple UDTF:

create or replace function COUNT_LOW_HIGH(LowerBound double, UpperBound double)
    returns table (MY_NUMBER double)
    LANGUAGE JAVASCRIPT
    AS
$$
{
    processRow: function get_params(row, rowWriter, context){
        for (var i = row.LOWERBOUND; i <= row.UPPERBOUND; i++) {
            rowWriter.writeRow({MY_NUMBER: i});
        }
   }
}
$$;

You can then call the UDTF using the TABLE function like this:

SELECT * FROM TABLE(COUNT_LOW_HIGH(1::double, 1000::double));

Snowflake Javascript SP output as Table?

select * from MONTHLY_SALES;

1 Answers