SnowflakeDocumentation Home

SSC-EWI-0030

The statement below has usages of dynamic SQL

Some parts in the output code are omitted for clarity reasons.

Severity

Medium

Description

This error is used to indicate that the statement has usages of dynamic SQL. Each specific source language has its own set of statements that can execute dynamic SQL. Dynamic SQL refers to code that is built as text using the string manipulation tools the database engine language provides.

This scenario is considered a complex pattern because dynamic SQL is built and executed in runtime making it more difficult to track and debug errors. This error is meant to be a helper to spot some problems that a static-code analyzer such as Snow Convert cannot.

Code Example

Teradata

IN -> Teradata_01.sql
REPLACE PROCEDURE teradata_dynamic_sql()
BEGIN
  DECLARE str_sql VARCHAR(20);
  SET str_sql = 'UPDATE TABLE
                    SET COLA = 0,
                        COLB = ''test''';

  EXECUTE IMMEDIATE str_sql;
  EXECUTE IMMEDIATE 'INSERT INTO TABLE1(COL1) VALUES(1)';
  EXECUTE str_sql;
  CALL DBC.SysExecSQL('INSERT INTO TABLE1(COL1) VALUES(1)');
END;

Oracle

IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE oracle_dynamic_sql
AS
    dynamic_statement VARCHAR(100);
    numeric_variable INTEGER;
    dynamic_statement VARCHAR(100);
    column_variable VARCHAR(100);
    cursor_variable SYS_REFCURSOR;
    c INTEGER;
    dynamic_statement VARCHAR(100);
BEGIN
    dynamic_statement := 'INSERT INTO sample_table(col1) VALUES(1)';
    numeric_variable := 3;
    column_variable := 'col1';

    EXECUTE IMMEDIATE dynamic_statement;
    EXECUTE IMMEDIATE 'INSERT INTO sample_table(col1) VALUES(' || numeric_variable || ')';

    OPEN cursor_variable FOR dynamic_statement;
    OPEN cursor_variable FOR 'SELECT ' || column_variable || ' FROM sample_table';
    OPEN cursor_variable FOR 'SELECT col1 FROM sample_table';

    
    c := DBMS_SQL.OPEN_CURSOR;
    dynamic_statement := 'SELECT * FROM sample_table';
    DBMS_SQL.PARSE(c, dynamic_statement);
END;