How to create SnowSQL query programmatically

Question

I am new to SnowFlake and exploring new things every day. I am stuck with the below scenario.

    SELECT 
   '{COL}' AS field_name,
    a.{COL}AS old_value,
    b.{COL}AS new_value FROM A JOIN B ON(...)
    WHERE a.{COL} != b.{COL}

I want to parameterize COL. And it may have multiple values like COL=col1,col2,col3. And then I want 3 queries separated by UNION as shown below.

    SELECT 
   'col1' AS field_name,
    a.col1 AS old_value,
    b.col1 AS new_value FROM A JOIN B ON(...)
    WHERE a.col1  != b.col1 

    UNION ALL

    SELECT 
   'col2' AS field_name,
    a.col2 AS old_value,
    b.col2 AS new_value FROM A JOIN B ON(...)
    WHERE a.col2  != b.col2 

    UNION ALL

    SELECT 
   'col3' AS field_name,
    a.col3 AS old_value,
    b.col3 AS new_value FROM A JOIN B ON(...)
    WHERE a.col3  != b.col3 

Is there any way to achieve this in SnowSQL ( SnowFlake )?

Answer 1

It's certainly possible to create dynamic SQL in SnowFlake. The most common way is to use Stored Procedures and/or UDFs using javascript to generate the SQL and execute it.

In the javascript you can use string replacement, loops, etc to create lists of parameters, join conditions etc.

Here's the general documentation

and here's a couple useful javascript snippits

//Set Up a multi-column Join Condition based on the columns in ColumnList
//Example output: "a.column1 = b.column1 AND a.column2 = b.column2"
ConditionArray = [];
ColumnList.forEach(function(column){
  conditionArray.push("a." + column+ " = " + "b." + column);
});
joinCondition = conditionArray.join(" AND ");


//Executing a script, and returning the output from a resultSet
//You can chain these calls for cleaner/shorter code
SQLQuery = "SELECT 1;"
SQLStatement = snowflake.createStatement({sqlText: SQLQuery });
SQLResultSet = SQL_Statement.execute();
SQLResultSet.next();
SQLResultSet.getColumnValue(1)

You can then create loops to execute multiple similar queries, or combine them together with UNIONs and then execute that.

https://docs.snowflake.net/manuals/sql-reference/stored-procedures-overview.html