1. EXECUTE IMMEDIATE in Snowflake
EXECUTE IMMEDIATE command in Snowflake executes SQL statements present in form a string literal (character sets enclosed by single quotes or double dollar signs). EXECUTE IMMEDIATE returns the result of the executed SQL statement.
The string literal that EXECUTE IMMEDIATE executes be can any of the following.
- A single SQL statement
- A Stored Procedure call
- An Anonymous Stored Procedure block
2. Executing SQL statements using EXECUTE IMMEDIATE
The syntax to execute SQL statements using EXECUTE IMMEDIATE is as follows.
EXECUTE IMMEDIATE '<sql_query>'; EXECUTE IMMEDIATE $$ <sql_query> $$;
The below example executes the SQL statement defined in a string literal using EXECUTE IMMEDIATE command.
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees';
3. Executing Stored Procedures using EXECUTE IMMEDIATE
The syntax to execute Stored Procedures using EXECUTE IMMEDIATE is as follows.
EXECUTE IMMEDIATE 'CALL <stored_procedure_name>'; EXECUTE IMMEDIATE $$ CALL <stored_procedure_name> $$;
The below example executes a stored procedure named
my_procedure() using EXECUTE IMMEDIATE command.
EXECUTE IMMEDIATE $$ CALL my_procedure(); $$;
4. Executing procedure blocks using EXECUTE IMMEDIATE
If you are running an anonymous block in Snowsight, you can execute the block directly without the need of EXECUTE IMMEDIATE command.
The following is an example of an anonymous block that you can run in Snowsight.
DECLARE net_sales NUMBER(38, 2); tax NUMBER(38, 2); gross_sales NUMBER(38, 2) DEFAULT 0.0; BEGIN net_sales := 98.67; tax := 1.33; gross_sales := net_sales + tax; RETURN gross_sales; END;
The output of the above anonymous block would be as follows:
But if you are using SnowSQL or the classic web interface, you must specify the block as a string literal (enclosed in single quotes or double dollar signs), and you must pass the block to the EXECUTE IMMEDIATE command as shown below.
EXECUTE IMMEDIATE $$ DECLARE net_sales NUMBER(38, 2); tax NUMBER(38, 2); gross_sales NUMBER(38, 2) DEFAULT 0.0; BEGIN net_sales := 98.67; tax := 1.33; gross_sales := net_sales + tax; RETURN gross_sales; END; $$ ;
5. Executing SQL statements in Stored Procedures using EXECUTE IMMEDIATE
The below stored procedure is an example which executes the SQL statements using EXECUTE IMMEDIATE.
- The first EXECUTE IMMEDIATE command executes the CREATE statement declared in the variable
- The second EXECUTE IMMEDIATE command executes the DELETE statement declared in variable
delete_stmtin concatenation with a filter condition passed as a string.
This also demonstrates that EXECUTE IMMEDIATE works not only with a string literal, but also with an expression that evaluates to a string (VARCHAR).
CREATE OR REPLACE PROCEDURE sp_execute_immediate_demo() RETURNS NUMBER LANGUAGE SQL AS $$ DECLARE create_stmt VARCHAR DEFAULT 'CREATE OR REPLACE TABLE temp_emp AS SELECT * FROM employees'; delete_stmt VARCHAR DEFAULT 'DELETE FROM temp_emp'; result NUMBER DEFAULT 0; BEGIN EXECUTE IMMEDIATE create_stmt; EXECUTE IMMEDIATE delete_stmt || ' WHERE status= ''INACTIVE'' '; result := (SELECT COUNT(*) FROM temp_emp); RETURN result; END; $$ ;
The output of the procedures gives the record count in table
temp_emp after removing the INACTIVE records.
6. EXECUTE IMMEDIATE with USING clause in Snowflake
The EXECUTE IMMEDIATE command is used in conjunction with USING clause to pass bind variables to the SQL query passed as a string literal to it. The bind variables are passed as a list separated by comma and enclosed in brackets.
The syntax to use EXECUTE IMMEDIATE with USING clause in Snowflake is as follows.
EXECUTE IMMEDIATE '<sql_query>' USING (bind_variable1, bind_variable2,…);
A bind variable holds a value to be used in SQL query executed by EXECUTE IMMEDIATE command.
The below stored procedure is an example in which values to the filter condition of a SQL query executed by EXECETE IMMEDIATE command are passed through bind variables defined in USING clause.
CREATE OR REPLACE PROCEDURE purge_data_by_date() RETURNS VARCHAR LANGUAGE SQL AS $$ DECLARE sql_stmt VARCHAR DEFAULT 'DELETE FROM employees WHERE hire_date BETWEEN :1 AND :2'; min_date DATE DEFAULT '2015-01-01'; max_date DATE DEFAULT '2017-12-31'; result VARCHAR; BEGIN EXECUTE IMMEDIATE sql_stmt USING (min_date, max_date); result := 'Data deleted between '|| min_date || 'and '|| max_date; RETURN result; END; $$ ;
7. EXECUTE IMMEDIATE with INTO clause in Snowflake
Using INTO clause in conjunction with EXECUTE IMMEDIATE, we can specify the list of the user defined variables which will hold the values returned by SELECT statement in Oracle. But currently EXECUTE IMMEDIATE with INTO clause is not supported in Snowflake like in Oracle.
Instead we can still assign the values to the user defined variables from the result of a SQL statement by using INTO clause directly in SELECT statement as shown below.
SELECT id, firstname INTO :id_variable, :name_variable FROM employees WHERE id = 101;
For more details refer our previous article.
Checkout other articles related to Snowflake Stored Procedures
- Snowflake Stored Procedures
Subscribe to our Newsletter !!
A Row Access Policy is a schema-level object that is assigned to a table or view and determines whether a given row in them can be viewed by a user.
Stored procedures allow you to write procedural code that executes business logic by combining multiple SQL statements.
A Variable is a named object which holds a value of a specific data type whose value can change during the stored procedure execution.