How to EXECUTE a STRING in Snowflake

Question

Following up from this thread

with a as (
  select * from hubspot.information_schema.tables
  where table_catalog = 'HUBSPOT' AND TABLE_SCHEMA = 'MONGODB' and table_name != '_SDC_REJECTED' and table_type = 'BASE TABLE'
),
b as (
  select * ,
    $$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
    replace(t,'TABLE_NAME',table_name) as sql
  from a
)
, test as (
select listagg(sql,'\nUNION ALL\n') within group (order by table_schema, table_catalog)
from b
    )
EXECUTE IMMEDIATE SELECT * FROM test
;

I have managed to create a select statement which unions a lit of tables from a specific schema. How do I then execute this string?

I've tried EXECUTE IMMEDIATE but I keep getting this error: 'Syntax error: unexpected 'EXECUTE'. (line 20)'

Answer 1

Snowflake script block could be used:

DECLARE 
  res RESULTSET;
  sqlQuery TEXT;
BEGIN
   WITH a as (
     select * 
     from hubspot.information_schema.tables
     where table_catalog = 'HUBSPOT' 
      AND TABLE_SCHEMA = 'MONGODB' 
      and table_name != '_SDC_REJECTED' 
      and table_type = 'BASE TABLE'
    ),b as (
       select * ,
       $$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
    replace(t,'TABLE_NAME',table_name) as sql
       from a
    ), test as (
    select listagg(sql,'\nUNION ALL\n') 
           within group (order by table_schema, table_catalog) AS query
     from b
    )
    SELECT query
    INTO :sqlQuery
    FROM test;

    res := (EXECUTE IMMEDIATE :sqlQuery);
   RETURN TABLE(res);
END;

Related: SELECT ... INTO ... and EXECUTE IMMEDIATE

Another option is session variable(though the length of string is limited to 256 bytes):

SET sqlQuery = (SELECT ... );
-- SELECT query should return single column/row

EXECUTE IMMEDIATE $sqlQuery;