Dynamic SQL in a Snowflake SQL Stored Procedure

Snowflake Dynamic query to run the sql from another table

The main problem is the try block was in the wrong place. There were some other errors. For example, when comparing values in JavaScript, use == or ===. Using = is only for assignments and evaluates incorrectly inside if expressions.

create or replace transient table T1 (ID int, SQL_TEXT string, SQL_NAME string);

insert into T1 (ID, SQL_TEXT, SQL_NAME) values
 (1,'Select current_date();', 'Test1'),
 (2,'Select current_timestamps();', 'Test2'),
 (3,'Select * from foobar10384 where t1 > 1000;', 'Test3'),
 (4,'select * from t2 where date < current_date();', 'Test4');
 
 
 create or replace procedure TestQuery (TABLE_NAME string, SQL_TEXT string, SQL_NAME string)
    returns string 
    not null
    language javascript
    as
    $$
    var op = "";
    // Dynamically compose the SQL statement to execute.
    var sqlCommand = `select ${SQL_TEXT}, ${SQL_NAME} from ${TABLE_NAME}`;
    // Prepare statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sqlCommand
           }
        );
    // Execute Statement
    
    var res = stmt.execute();

    while (res.next())  {
        v_col_name = res.getColumnValue("SQL_TEXT");
        sqlcommand2 = "explain using tabular " + v_col_name
        var stmt2 = snowflake.createStatement(
           {
           sqlText: sqlcommand2
           }
        );
        try {
             var res2 = stmt2.execute();
        } catch (err) {
             if (op != "") op += ",";
             op += res.getColumnValue("SQL_NAME");
        }
    }
    if (op == "") {
    return "all success"
    }    
    else {
    return op}    
    $$;    

call testquery('T1', 'SQL_TEXT', 'SQL_NAME'); 

Edit with a final thought: Since this is going through the work of identifying bad SQL, it should return the reason it's bad. This refactor returns an array. The array will be empty if there are no SQL errors. It will be filled with one or more objects containing the name of the SQL with an error and the error itself:

 create or replace procedure TestQuery (TABLE_NAME string, SQL_TEXT string, SQL_NAME string)
    returns array 
    not null
    language javascript
    as
    $$
    var op = [];
    var err = {};
    // Dynamically compose the SQL statement to execute.
    var sqlCommand = `select ${SQL_TEXT}, ${SQL_NAME} from ${TABLE_NAME}`;
    // Prepare statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sqlCommand
           }
        );
    // Execute Statement
    
    var res = stmt.execute();

    while (res.next())  {
        v_col_name = res.getColumnValue("SQL_TEXT");
        sqlcommand2 = "explain using tabular " + v_col_name
        var stmt2 = snowflake.createStatement(
           {
           sqlText: sqlcommand2
           }
        );
        try {
             var res2 = stmt2.execute();
        } catch (e) {
             err = {};
             err.SQL_NAME = res.getColumnValue("SQL_NAME");
             err.SQL_ERROR = e.message;
             op.push(err);
        }
    }
    return op
    $$;

Output:

[
  {
    "SQL_ERROR": "SQL compilation error:\nUnknown function CURRENT_TIMESTAMPS",
    "SQL_NAME": "Test2"
  },
  {
    "SQL_ERROR": "SQL compilation error:\nObject 'FOOBAR10384' does not exist or not authorized.",
    "SQL_NAME": "Test3"
  },
  {
    "SQL_ERROR": "SQL compilation error: error line 1 at position 45\ninvalid identifier 'DATE'",
    "SQL_NAME": "Test4"
  }
]

Snowflake dynamic SQL: Equivalent of DBMS_SQL/sp_executesql

It is possible using Snowflake Scripting:

DROP TABLE IF EXISTS wide5;

-- to be run with Snowsight
DECLARE
    SQL STRING;
BEGIN
    SELECT create_table_sql 
    INTO :SQL 
    FROM ( 
     SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())'
           , '<index>', seq8()+1),'')
           WITHIN GROUP(ORDER BY seq8()) AS column_list

       ,REPLACE(REPLACE(
              'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
              ,'<table_name>', 'wide5')
              ,'<column_list>', column_list) AS create_table_sql
     FROM TABLE(GENERATOR(rowcount => 5))
    );

    EXECUTE IMMEDIATE :SQL;
END;

SELECT * FROM wide5;
-- ID   A1  A2  A3  A4  A5

Is it possible to generate dynamic sql to return rows with various columns in snowflake

Given this input:

create or replace table t as
select parse_json($1) my_json
from values
('{ "FruitShape":"Round", "FruitSize":55 } '),
('{ "FruitShape":"Square"  } '),
('{ "FruitShape":"Oblong", "FruitSize":22, "FruitColor":"Chartreuse" } ')
;

This query will generate the dynanmic SQL:

select 'select ' 
  || (select listagg(distinct 'my_json:'||key::text, ',') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
  || ' from t;';

Generated SQL and it's output:

select my_json:FruitShape, my_json:FruitSize, my_json:FruitColor from t;

MY_JSON:FRUITSHAPE | MY_JSON:FRUITSIZE | MY_JSON:FRUITCOLOR
-------------------+-------------------+-------------------
"Round"            | 55                | NULL              
"Square"           | NULL              | NULL              
"Oblong"           | 22                | "Chartreuse"      

This stored procedure will execute dynamic SQL without cut-and-paste:

create or replace procedure p()
returns string
language javascript
strict
execute as caller
as
$$
  const statement1 =  `
    select 'select ' 
      || (select listagg(distinct 'my_json:'||key::text, ', ') from t, lateral flatten(input=>t.my_json, mode=>'OBJECT')) 
      || ' from t'
  `
  const rs1 = snowflake.execute ({sqlText: statement1})
  rs1.next()
  const statement2 = rs1.getColumnValue(1)
  const rs2 = snowflake.execute ({sqlText: statement2})
  return 'SUCCESS'
$$
;

Then you can call the stored procedure and collect the results:

call p();
select * from table(result_scan(-2))

You mentioned constraining the output depending on some metadata. You can do that in the dynamic SQL, for example by filtering the distinct list of fields.

Credits to davidgarrison for the result_scan() technique!

Hope that's helpful.

Related Topics