Snowflake execute immediate into variable

Question

I have a table in which one column contains SQL query that I want to execute. Those queries are some kind of quality check(for instance "select count(*) from the table where col = x"). So, now I iterate over rows from that table, take a specific query and I want to execute it, and take the actual value(result) from that query into a variable. Is this possible with Snowflake scripting?

Answer 1

Yes, If I understand what you want by "a variable"..

Little bit of setup:

CREATE table test.test2.to_do(checks string);

INSERT INTO to_do values ('select count(*) from test.test2.to_do;');

• Now some Snowflake Scripting to loop across a cursor from the "checks table"
• Then run that SQL
• Then capturing the results into a variable

declare
  counts int;
  total_counts int := 0; 
  c1 cursor for select checks from test.test2.to_do;
begin

  for record in c1 do
    execute immediate record.checks;
    
    select $1 into counts from table(result_scan(last_query_id()));
    total_counts := total_counts + counts;
  end for;
  return total_counts;
end;

anonymous block
1

Boost those Checks:

INSERT INTO to_do values ('select sum(10) from test.test2.to_do;');

select * from test.test2.to_do;

CHECKS
select count(*) from test.test2.to_do;
select sum(10) from test.test2.to_do;

Run the script SQL above again:

anonymous block
22

But can we direct inject?

INSERT INTO to_do values ('select count(*) into counts from test.test2.to_do;');
INSERT INTO to_do values ('select sum(10) into counts from test.test2.to_do;');

alter the block:

declare
  counts int;
  total_counts int := 0; 
  c1 cursor for select checks from test.test2.to_do;
begin

  for record in c1 do
    execute immediate record.checks;
    
    --select $1 into counts from table(result_scan(last_query_id()));
    total_counts := total_counts + counts;
  end for;
  return total_counts;
end;

and run:

Error: 'STATEMENT_ERROR' on line 8 at position 4 : SQL compilation error: error line 1 at position 0 INTO clause is not allowed in this context (line 8)

NO blocked, so last query it is..