MSCEWI3109

Expressions as arguments of Using Clause are not supported by Snowflake Scripting

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

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)

Example Code

Input Code:

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;

Output Code:

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;

Manually migrated Execute Immediate procedure:

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;
$$;

Recommendations

• Procedures can be manually migrated by adding a variable and then assigning the expression to said variable.

• If you need more support, you can email us at [email protected]