Snowflake - execute immediate returns query text rather than the query result

I have a table that has a column that contains SQL queries used in analyses. I am trying to use execute immediate to return the results of those SQL queries.

Ex:

INSERT INTO ANALYSIS_QUERIES(DIR_NAME, FILE_NAME, THE_SQL) VALUES
(
'COUNT_CHECK'
,'USER_COUNT_CHECK.sql'
,'select
    count(*) as usr_ct
from users;'
);

If I run

execute immediate 'select the_sql from analysis_queries'

it returns the same thing as if I had just run:

select the_sql from analysis_queries;

which is select count(*) as usr_ct from users;

What I want to return is 125 (the actual usr_ct).

I've tried

set stmt = (select the_sql from analysis_queries);
execute immediate $stmt;

Which does return the usr_ct, but there are longer queries where it throws an error Assignment to 'STMT' not done because value exceeds size limit for variables. Its size is 1,902; the limit is 256 (internal storage size in bytes).

I'm new to dynamic sql/stored procedures, so any help would be amazing.

Thank you,

Warning!

Running an arbitrary query provided by user is NOT secure, for instance malicious user may provide sql_text like DROP DATABASE/DROP TABLE/ALTER USER/....

Query table:

CREATE OR REPLACE TABLE ANALYSIS_QUERIES(DIR_NAME TEXT, 
                                         FILE_NAME TEXT, 
                                         THE_SQL TEXT, 
                                         RESULT INT) -- result column
AS 
SELECT 'COUNT_CHECK','USER_COUNT_CHECK.sql', 
       'select count(*) as usr_ct from users;', NULL;

-- 125 rows users table
CREATE OR REPLACE TABLE users(col int) AS 
SELECT seq4()
FROM TABLE(GENERATOR(ROWCOUNT=>125));

SELECT * FROM ANALYSIS_QUERIES;

Executing code using Snowflake Scripting block:

• CURSOR and FOR CURSOR LOOP to iterate over analysis_queries table
• EXECUTE IMMEDIATE to run the query
• RESULT_SCAN(LAST_QUERY_ID()) - intercepting the output and assigning to variable

Code:

DECLARE
   cur CURSOR FOR SELECT * 
                  FROM ANALYSIS_QUERIES
                  --WHERE ...  -- some condition if needed
                  ;
   sql_text TEXT;
   dir_name TEXT;
   file_name TEXT;
   res INT;
BEGIN
   FOR c IN cur DO
      sql_text := c.THE_SQL;
      dir_name := c.DIR_NAME;
      file_name := c.FILE_NAME;
     
      -- run the query 
      EXECUTE IMMEDIATE sql_text;

      -- intercept result(assumption single row/single column) to variable
      res := (SELECT $1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));

      -- save result back into table
      UPDATE ANALYSIS_QUERIES
      SET result = :res
      WHERE DIR_NAME = :dir_name
        AND FILE_NAME = :file_name;
   END FOR;

   RETURN 'Success';
END;

Output:

 SELECT * FROM ANALYSIS_QUERIES;

Approach 2:

> I've tried
>
> set stmt = (select the_sql from analysis_queries);
> execute immediate $stmt;

Using Snowflake Scripting block:

BEGIN 
   LET THE_SQL TEXT := (SELECT THE_SQL 
                        FROM ANALYSIS_QUERIES 
                        WHERE FILE_NAME = 'USER_COUNT_CHECK.sql');

   LET RES RESULTSET := (EXECUTE IMMEDIATE :THE_SQL);

   RETURN TABLE(RES);
END;

Output:

Related: Using Snowflake Scripting in SnowSQL and the Classic Console