Snowflake dynamic SQL: Storing query results as variables between executions

Question

In Snowflake, I'd like to use the numeric result of one dynamic sql query in a second dynamic sql query:

CREATE OR REPLACE PROCEDURE DB.SCHEMA.SP_DATA_COLUMN_VALUES(table_name varchar, column_name varchar, date_column varchar)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
-- Finds most common values associated with a column.
  DECLARE
    res RESULTSET;
    sql_query VARCHAR;
    sql_row_count varchar;
    total_row_count varchar;
  BEGIN
    sql_row_count := 'select count(*) from '|| table_name ||' where '|| date_column ||' > DATEADD(day, -365, getdate());';
    total_row_count := (execute immediate :sql_row_count);
    sql_query := 'select '|| column_name ||', iff('|| total_row_count ||' = 0, 0.00, cast(count(*) as numeric(18,2))/'|| total_row_count ||'*100) PercentOfDataSet' ||
                 ' from '|| table_name ||' where '|| date_column ||'> DATEADD(day, -365, getdate())' ||
                 ' group by 1 order by 2 desc limit 1000;';
    res := (EXECUTE IMMEDIATE :sql_query);
    RETURN TABLE(res);
  END;
$$
;

The goal is to find the top 1000 values for any column in any table, and how frequently the values appear across the dataset in a given timeframe.

However, I'm getting the error: Invalid expression value (?SqlExecuteImmediateDynamic?) for assignment. Is there any way to store the result of the first query to a variable that can be used in the second?

I was expecting the output:

<column_name>, PercentOfDataSet
Value1, X.XX
Value2, X.XX
Value3, X.XX

I think we could separate this question in 2: The one I see here is dealing with the error of invalid expression value. But there's a 2nd question about getting the most frequent value for a column - and that should be a separate one. — Felipe Hoffa, May 26 '23 at 22:51
For the underlying question, check https://stackoverflow.com/q/76344643/132438 — Felipe Hoffa, May 26 '23 at 23:09

score 0 · Answer 1 · answered May 26 '23 at 21:46

The query could be simplified with IDENTIFIER:

sql_row_count := 'select count(*) from '|| table_name ||' where '|| date_column ||' > DATEADD(day, -365, getdate());';
total_row_count := (execute immediate :sql_row_count);

=>

total_row_count := (SELECT count(*) 
                    FROM IDENTIFIER(:table_name)
                    WHERE IDENTIFIER(:date_column) > DATEADD(day, -365, getdate());

Snowflake dynamic SQL: Storing query results as variables between executions

1 Answers1

Linked