can we use dynamic sql in snowflake scripting?

Question

We make extensive use of dynamic SQL in Snowflake Stored Procedures for many reasons.

One of them being that we have several databases, each prefixed with the environment name. Like PROD_DB1, PROD_DB2, etc...

Our code is not specific to one environment so all of our stored procedures start by checking which environment they are executing in (looking at the prefix of CURRENT_DATABASE()). Then we adapt our table/view names accordingly when doing cross database stuff.

This is just one example of the various reasons for dynamic SQL.

Is there a way to do this sort of things in Snowflake SQL (as opposed to Stored procedures using javascript)?

Answer 1

Is there a way to do this sort of things in Snowflake SQL (as opposed to Stored procedures using javascript)?

Yes, it is possible to build arbitrary query and execute it using with Snowflake Scripting. EXECUTE IMMEDIATE:

Executes a string that contains a SQL statement or a Snowflake Scripting statement.

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

Related: Snowflake dynamic SQL: Equivalent of DBMS_SQL/sp_executesql, Dynamic SQL in a Snowflake SQL Stored Procedure