Executing a SQL statement, present in a table, and returning the resulting record set in Snowflake

Question

I need help with a practical scenario. I have a table called CONFIG_TBL in Snowflake. This table has SQL statements, 1 per row. My goal is to use Snowflake Stored Procedures or Snowflake UDF, or a combination of the two, and return a result set that will be obtained after execution of that statment.

The statements are simple select statements, like "select * from ABC'.

I could have done this, very easily in SQL server, since procedures can return table values. However, I don't know how to do this in Snowflake.

Any help will be greatly appreciated.

Thanks in advance.

How many rows will result from the selects? You cannot return tables from Snowflake stored procedures, but you can return delimited strings or variants with JSON up to 16 mb. — Greg Pavlik
That is truly a limitation! The data I want to return is over 16mb. — prateek ahir
Best thing to do in that case is write it to a temp table. Let me know if you want a code sample. — Greg Pavlik

David Garrison David Garrison · Accepted Answer · 2020-07-10T15:19:53

Here's something to get you started at least. Procedures use javascript (SQL Stored Procedures are coming soon), but they can be used to run dynamic queries like you are looking for.

You can get the results in a couple of ways. By either returning a variant object or by using result_scan after calling the procedure.

This example just runs one query so your final solution will be different depending on just what you want the output to look like.

CREATE OR REPLACE PROCEDURE SCHEMA.PROCEDURE_NAME()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
retrieve_queries_sql = "select top 1 query from CONFIG_TBL";
retrieve_queries_result_set = snowflake.execute({sqlText: retrieve_queries_sql });

query_to_run = retrieve_queries_result_set.next().getColumnValue(1);

rs = snowflake.execute({sqlText: query_to_run})

var return_value = "";
if (rs.next())  {
      return_value += rs.getColumnValue(1);
      return_value += ", " + rs.getColumnValue(2);
      }
while (rs.next())  {
      return_value += "\n";
      return_value += rs.getColumnValue(1);
      return_value += ", " + rs.getColumnValue(2);
      }
  }
return return_value;
$$

CALL SCHEMA.PROCEDURE_NAME()

SELECT *
FROM table(result_scan(last_query_id()))

Edit: Fixed to have example correctly return a result which can then be used by the result_scan. Example taken from here. There are various more examples for getting results out of a procedure, including using JSON output.

Executing a SQL statement, present in a table, and returning the resulting record set in Snowflake

1 Answers