Snowflake how to return query string from execute method

user_11058930 0
Viewed 363

I have a Snowflake procedure, for brevity, lets just say it just runs a query using snowflake.execute

var rs = snowflake.execute( { sqlText:
`
  CREATE OR REPLACE VIEW DATABASE.SCHEMA.VIEW_NAME_HERE AS
  SELECT
    column1
    ,${stuff}     as column2
    ,${morestuff} as column3

  FROM DATABASE.SCHEMA.TABLE_SOURCE_HERE
`

                            } );
  return rs

How do i return rs so that it shows the query that it's running? I'm just getting an [object object] result.

snowflake-cloud-data-platform sql
1 Answers

The Statement object has a method called getSqlText():

https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html#getSqlText

CREATE OR REPLACE PROCEDURE my_proc() 
RETURNS STRING 
LANGUAGE JAVASCRIPT 
EXECUTE AS OWNER
AS 
$$ 
    snowflake.execute( {sqlText: "create or replace table test (col string)"} ); 
    var column = 'col';
    var stmt = snowflake.createStatement( {sqlText: `SELECT MAX(${column}) FROM test`} ); 
    var ret = stmt.execute();
    
    return stmt.getSqlText();
$$ ; 

call my_proc();

It returns:

SELECT MAX(col) FROM test
user_98793790
Related