Print Snowflake Query in stored procedure

Question

I am trying to use Snowflake stored procedures and would like to print the SQL statement in the stored procedure below to the screen.

CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      var command = "SELECT * FROM western_provinces ORDER BY province;"
      var stmt = snowflake.createStatement( {sqlText: command } );
      var rs = stmt.execute();
      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);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return return_value;
  $$
  ;

How can I print the value of command variable (which contains the SQL) to the screen whenever stored procedure is executed?

thanks

Answer 1

I haven't found a way to do this, but there are a few options to get close at least.

1. You can insert output into a table.

2. You can return a variant instead with debugging output in an array or similar

   return { "output":return_value, "debug":result }
   

3. Use a procedure to replicate something similar. See similar post from the snowflake community: https://community.snowflake.com/s/question/0D50Z00009Uu3cTSAR/debugging-procedures-technicques-print-std-out-etc)

Answer 2

Use getSqlText() to return a statement as text, see docs.

Add the return statement from below to the end of your procedure to have it print the statement when it is called.

CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      var command = "SELECT * FROM western_provinces ORDER BY province;"
      var stmt = snowflake.createStatement( {sqlText: command } );
      var rs = stmt.execute();
      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);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return stmt.getSqlText(); -- < will print/return the statement text
  $$
  ;

Answer 3

Just use call method after the procedure.

1.CALL read_western_provinces('database name','schema name');

Syntax

CALL ('database_name','schema_name');