Snowflake Scripting: Changes to global variables (Pending)¶

When this behavior change bundle is enabled, the Snowflake Scripting global variables for DML commands behave differently. These variables are described in Determining the number of rows affected by DML commands.

When a non-DML statement is executed after the last DML statement in a Snowflake Scripting block or stored procedure, these variables behave as follows:

Before the change:

The variables return the value set by the last DML statement:

• SQLROWCOUNT - Number of rows affected by the last DML statement.

• SQLFOUND - TRUE or FALSE based on the last DML statement.

• SQLNOTFOUND - TRUE or FALSE based on the last DML statement.

After the change:

The variables return NULL.

For example, the following Snowflake Scripting anonymous block returns different values before and after the change:

EXECUTE IMMEDIATE
$$
BEGIN
  CREATE OR REPLACE TABLE test_snowflake_scripting_gv (i INT);
  INSERT INTO test_snowflake_scripting_gv VALUES (1);
  SELECT 1;
  RETURN SQLROWCOUNT;
END;
$$;

Returned value before the change::

+-----------------+
| anonymous block |
|-----------------|
|               1 |
+-----------------+

Returned value after the change::

+-----------------+
| anonymous block |
|-----------------|
|            NULL |
+-----------------+

To achieve the previous behavior after the change, save the Snowflake Scripting global variable value in a new variable before subsequent non-DML statements, and then return the value of the new variable. For example:

EXECUTE IMMEDIATE
$$
BEGIN
  LET sql_row_count_var := 0;
  CREATE OR REPLACE TABLE test_snowflake_scripting_gv (i INT);
  INSERT INTO test_snowflake_scripting_gv VALUES (1);
  sql_row_count_var := SQLROWCOUNT;
  SELECT 1;
  RETURN sql_row_count_var;
END;
$$;

Ref: 1850