Use Cases for Dynamic SQL
Dynamic SQL allows you to create and manipulate a string, and then run the resulting string as a SQL statement. Snowflake supports dynamic SQL using the identifier keyword and table() function. There are some notable exceptions; however, where the Snowflake SQL parser currently does not support dynamic SQL.
For example, suppose you want to unload data to stage on a daily basis. To keep the data organized, you decide to put the unloaded data into paths with the current date, like this:
@my_stage/2021-01-22/data.csv -- Data unloaded daily, organized by date @my_stage/2021-01-23/data.csv
To accomplish this, you want a single line of SQL to run on a daily basis with the date dynamically generated. The problem is the path in a stage is a string literal that currently does not support using the identifier keyword, variables, or other dynamic SQL methods.
Snowflake Stored Procedures for Dynamic SQL
Writing a stored procedure is one option to run dynamic SQL along these lines. External calls from something like Python or Java can generate and run dynamic SQL. One disadvantage of external code to run dynamic SQL is that it requires external dependencies to schedule and run the code. Stored procedures elimination of any external dependencies offers a major advantage. Because Snowflake tasks also require no external dependencies, it’s possible schedule and run dynamic SQL with no external dependencies.
There are two options for stored procedures to run dynamic SQL. One option is to build the SQL statement inside the stored procedure code. While this approach has advantages, it has a major disadvantage. It requires the creation and maintenance of a new stored procedure for each dynamic SQL statement to run. The other approach is to generate SQL statements outside the stored procedure that a single general-purpose stored procedure runs.
That is the approach the following stored procedure uses. It’s intended to run general-purpose dynamic SQL generated outside the procedure and passed in as a parameter.
For example in the previously cited example, this allows running the daily data offload like this:
call run_dynamic_sql('copy into @mystage/' || current_date || '/data.csv from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION file_format = (type = CSV)');
A General-Purpose Stored Procedure to Run Dynamic SQL
This stored procedure will run any SQL statement that can be run in a Snowflake stored procedure. The procedure will return a JSON object, either an error indication of a JSON document containing the rows from the execution. Since Snowflake JSON documents have a 16 Mb limit, the stored procedure should return only small result sets. Although intended to execute non-query statements, because it returns a JSON you can use it to convert select query results to JSON.