Dynamic SQL Queries in Snowflake Scripting

Dynamic SQL is one of the important features that many databases support. Dynamic SQL allows you to write SQL queries during runtime. With the help of dynamic SQL, you can build your SQL query on the fly and execute them. 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 use dynamic SQL queries in Snowflake scripting.

Dynamic SQL Queries in Snowflake

Snowflake supports writing 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.

Snowflake support dynamic SQL queries in blocks and stored procedure.

How to use Dynamic SQL in Snowflake Scripting?

Snowflake scripting support EXECUTE IMMEDIATE statement which executes a string that contains a SQL statement. You can write execute immediate inside stored procedures and a Snowflake Scripting block.

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 DML or DDL SQL statement.
A stored procedure call.
A control-flow statement (e.g. looping or branching statement).
A Snowflake scripting 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 1 as col';
+------+
|  COL |
|------|
| 1    |
+------+

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_test()
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;
$$;

call execute_immediate_local_variable()

Snowflake Scripting Block with Dynamic SQL

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

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