MSCEWI3109
Expressions as arguments of Using Clause are not supported by Snowflake Scripting
Medium
Oracle supports using expressions as arguments to any USING Clause for the EXECUTE IMMEDIATE statements. This functionality is not supported by Snowflake Scripting, so the argument gets commented out and this EWI is emitted.
Snowflake Scripting does support variable expressions, and thus it is possible to replace the expression by manually assigning it to a variable (see example below)
CREATE TABLE immediate_table(col1 INTEGER);
CREATE OR REPLACE PROCEDURE expression_arguments
IS
immediate_input INTEGER := 0;
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO immediate_table VALUES (:value)' USING immediate_input+1;
END;
CALL expression_arguments();
SELECT * FROM immediate_table; --RETURNS 1;
CREATE OR REPLACE TABLE PUBLIC.immediate_table (col1 INTEGER);
CREATE OR REPLACE PROCEDURE PUBLIC.expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
immediate_input INTEGER := 0;
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO PUBLIC.immediate_table VALUES (?)' USING (
-- ** MSC-ERROR - MSCEWI3109 - EXPRESSIONS AS ARGUMENTS OF USING CLAUSE IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
-- immediate_input+1
);
END;
$$;
CALL PUBLIC.expression_arguments(); -- Compile error
SELECT * FROM PUBLIC.immediate_table; --RETURNS nothing, proc didn't run;
Replacing this procedure with the one above will solve the compilation error, and yield the same results as Oracle.
CREATE OR REPLACE PROCEDURE PUBLIC.expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
immediate_input INTEGER := 0;
using_argument_variable INTEGER;
BEGIN
using_argument_variable := immediate_input+1;
EXECUTE IMMEDIATE 'INSERT INTO PUBLIC.immediate_table VALUES (?)' USING (using_argument_variable );
END;
$$;
- Procedures can be manually migrated by adding a variable and then assigning the expression to said variable.
Last modified 3mo ago