Snowflake how to return query string from execute method

Question

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.

Eric Lin · Accepted Answer · 2021-10-21 04:52:40Z

3

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

answered Oct 21, 2021 at 4:52

Eric Lin

1,5608 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mikelowry Over a year ago

Got it, so I must use createStatement in order to achieve what I'm trying to do? I can't call getSqlTest or something equivalent from the execute method?

Eric Lin Over a year ago

That's right. snowflake.execute() returns ResultSet object, not Statement object: docs.snowflake.com/en/sql-reference/… And getSqlText() is a method from Statement Object.

Collectives™ on Stack Overflow

Snowflake how to return query string from execute method

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related