execute immediate with $$

Question

I have a long query that returns 1 column and 1 row:

Query 1:

select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);

The output looks like this. It has a query that I want to run. Note that it consists of quotation marks. The original query 1 also has quotation marks in the LISTAGG.

output:

test_query   
select "col1", "col2" from stg.new

I wanted to extend the Query A such that I can select and run the test_query and return the final output of the test_query instead of queryA. I tried using execute immediate with $$:

execute immediate 
$$select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx)$$;

but instead of running the test_query it outputs the same thing as Query1.

What else can I try?

You need to set the result of your first query (that builds the SQL string) to a variable and then execute that string. There are plenty of examples of this in the Snowflake documentation for example: https://docs.snowflake.com/en/sql-reference/sql/execute-immediate.html — NickW, Nov 21 '22 at 12:59
I had already tried this but I get this ```Assignment to 'STMT' not done because value exceeds size limit for variables. Its size is 1,122; the limit is 256 (internal storage size in bytes).``` since the query is long — x89, Nov 21 '22 at 13:01
From your comment here it seems that you're trying to assign the result to a SQL variable (identified with a dollar sign). A Scripting variable is identified with a colon and allows longer strings. — Greg Pavlik, Nov 21 '22 at 14:39
can you write an answer with an example of what you mean? @GregPavlik — x89, Nov 21 '22 at 14:41

score 0 · Answer 1 · answered Nov 21 '22 at 19:52

Here's an attempted conversion of your SQL template. You can extend it to add the entire SQL statement:

execute immediate
  $$
    declare 
         rs resultset default (
select test_query from (
SELECT 
     LISTAGG('...') ... AS xx,
     LISTAGG('"' || c.COLUMN_NAME || '"', ', ') WITHIN GROUP(ORDER BY c.COLUMN_NAME) AS column_list
     .....
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = xx);
         );
         c cursor for rs;
         sqlStatement string;
         rsFinal resultset;
    begin
         for rowContents in c do
            sqlStatement := rowContents.SQL_STATEMENT;
         end for;
         rsFinal := (execute immediate :sqlStatement);
         return table(rsFinal);
    end;
  $$
  ;

In case that's not working because of context, etc., here is a completely self-contained sample that's working:

create or replace transient table T1 as
select 'Hello world' as s;

create or replace transient table SQL_TO_RUN as
select 'select S from T1 SQL_STATEMENT' SQL_STATEMENT
;

execute immediate
  $$
    declare 
         rs resultset default (select SQL_STATEMENT from SQL_TO_RUN);
         c cursor for rs;
         sqlStatement string;
         rsFinal resultset;
    begin
         for rowContents in c do
            sqlStatement := rowContents.SQL_STATEMENT;
         end for;
         rsFinal := (execute immediate :sqlStatement);
         return table(rsFinal);
    end;
  $$
  ;

Output:

S
Hello world

execute immediate with $$

1 Answers1