How to use Show Command in Stored Procedures - Not working

Question

Following the bog post https://community.snowflake.com/s/article/How-to-USE-SHOW-COMMANDS-in-Stored-Procedures

The result I get it NaN, which makes sense since the return value is set to float in this Blog Post.

I have tried setting the return value to varchar, string etc. but Iget different results like Object object etc.

CREATE OR REPLACE PROCEDURE SHOP(CMD VARCHAR) returns float not null LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ var stmt = snowflake.createStatement( { sqlText:${CMD} } );

var res = stmt.execute(); return res $$ ;

The expected return is a list of tables, actual results are NaN.

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-11-12T17:08:05

The stmt.execute() call returns a ResultSet object that will become NaN when converted to FLOAT. You need to use the ResultSet object to extract the data returned from the query.
Also remember variable references in JavaScript are plain, ie. CMD.

The PROCEDURE return type FLOAT will not work if you want "The expected return is a list of tables", whatever that is. I suspect you want ARRAY or the totally flexible VARIANT. Both work with the following:

$$
  var stmt = snowflake.createStatement( { sqlText: CMD } );
  var rs = stmt.execute();
  var result = [];
  while (rs.next())
    result.push(rs.getColumnValue(1));
  return result;
$$;

Please look at the Stored Procedures API documentation for details.

If you have multiple columns, the code becomes slightly more complicated:

  var result = [], row, col;
  while (rs.next()) {
    row = [];
    for (col = 1; col <= stmt.columnCount; col++)
      row.push(rs.getColumnValue(col));
    result.push(row);
  }

Hardcore JavaScript programmers might compress this to:

var result = [], cols = Array.from({ length: stmt.columnCount }, (v, i) => i + 1);
while (rs.next()) result.push(cols.map(c => rs.getColumnValue(c)));

A final variant where the first result row contains the column names and the following rows contain the data from the result set, accessible as result[row][column]:

var result =
    [ Array.from({ length: stmt.columnCount }, (v, i) => stmt.getColumnName(i + 1)) ];
while (rs.next()) result.push(result[0].map(cn => rs.getColumnValue(cn)));

How to use Show Command in Stored Procedures - Not working

1 Answers