Snowflake Dynamic SQL Queries and Examples

When you migrate the data from other relational databases such as Netezza, Oracle, Redshift, etc, you will find the many queries which are generated on the fly and executed. These kind of queries are called dynamic queries. In the data warehouse, there may be a situation where you want to generate dynamic SQL queries based on your requirement. In this article, we will check how to generate and execute dynamic SQL queries in Snowflake with an example.

Snowflake Dynamic SQL Queries

Snowflake supports generating and executing dynamic queries in stored procedures. A stored procedure can dynamically construct SQL statements and execute them. For example, you could build a SQL command string that contains a mix of pre-configured SQL and user inputs such as procedure parameters.

However, Snowflake does not support dynamic SQL queries outside stored procedures.

Snowflake supports writing dynamic SQL in Snowflake scripting and using the JavaScript API

Snowflake Scripting Dynamic SQL
Snowflake Dynamic SQL in JavaScript Stored Procedures

Let us check these two methods in detail.

How to use Dynamic SQL in Snowflake Scripting?

Snowflake scripting support EXECUTE IMMEDIATE statement which executes a string that contains a SQL statement.

Following is the syntax;

EXECUTE IMMEDIATE '<string_literal>'
    [ USING (bind_variable_1 [, bind_variable_2 ...] ) ] ;

In the above syntax string literal can be;

A single SQL statement.
A stored procedure call.
A control-flow statement (e.g. looping or branching statement).
A block.

EXECUTE IMMEDIATE returns the result of the executed statement. For example, if the string contained a SELECT statement, then the result set of the SELECT would be returned.

Snowflake Scripting Dynamic SQL Examples

Following example executes a SELECT statement defined in a string literal:

execute immediate 'SELECT PI()';
+-------------+
|        PI() |
|-------------|
| 3.141592654 |
+-------------+

Following example executes a statement defined in a session variable:

set stmt =
$$
    select 1 as COL;
$$
;
execute immediate $stmt;
+-----+
|COL  |
|-----|
| 1   |
+-----+

Snowflake Scripting Stored Procedure with Dynamic SQL

Following example executes statements that are defined in two local variables inside a Snowflake scripting stored procedure.

create procedure execute_immediate_local_variable()
returns varchar
as
 $$
     declare
        v1 varchar default 'CREATE TABLE temp1 (i INTEGER)';
        v2 varchar default 'INSERT INTO temp1 (i) VALUES (76)';
        result integer default 0;
     begin
        execute immediate v1;
        execute immediate v2  ||  ',(80)'  ||  ',(84)';
        result := (select sum(i) from temp1 order by i);
        return result::varchar;
     end;
$$;

Snowflake Dynamic SQL in JavaScript Stored Procedures

As mentioned earlier, Snowflake allows you to construct queries dynamically and execute using JavaScript API.

The JavaScript API object “snowflake” provides a “createStatement” method. You can use this statement to prepare the dynamic query that you are trying to execute. Further, you can use “execute” method to execute prepared query string.

Steps to Create Dynamic Queries in Snowflake

Following steps allows you to create dynamic queries

Define query string

var sql_command = "your SQL statement";

Prepare SQL statement

var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );

Execute SQL Statement

var res = stmt.execute();

Snowflake Dynamic SQL Example

Now, let us put all the above mentioned steps together and generate dynamic SQL queries in stored procedures.

For example,

Following stored procedure accepts the table name as an argument and returns the row count. It uses the dynamic SQL feature to prepare and execute SQL query.

create or replace procedure getRowCount(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    var sqlCommand = "select count(*) from " + TABLE_NAME;
    // Prepare statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sqlCommand
           }
        );
	// Execute Statement
    var res = stmt.execute();
    res.next();
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;

Note that, input parameter name is uppercased in stored procedure body.

Now, execute the procedure get row count of s_student table.

call getRowCount('S_STUDENT');
+-------------+
| GETROWCOUNT |
|-------------|
|           4 |
+-------------+

Snowflake Stored Procedure Example to Create Tables Dynamically

You can use the Snowflake stored procedure to create Snowflake tables dynamically. You can pass the table name dynamically and create. Don’t forget to use IDENTIFIER function to refer table name as a parameter.

Following stored procedure dynamically create three tables.

CREATE OR REPLACE PROCEDURE create_table_sp()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
    var tables = ['test_a', 'test_b', 'test_c'];
    
    for (var table of tables) {
        try {
            snowflake.execute(
                {
                 sqlText: `CREATE OR REPLACE TABLE IDENTIFIER(?)(ename VARCHAR);`
                ,binds: ['TEST_DB.public.' +  table]
                }
            );
        }
        catch(err){
            return "Failed: " + err;
        };
    };
    
    return "Succeded.";
$$;

Following is the output.

call create_table_sp();
+-----------------+
| CREATE_TABLE_SP |
|-----------------|
| Succeded.       |
+-----------------+