Determining the number of rows affected by DML commandsΒΆ

After a DML command is executed (excluding the TRUNCATE TABLE command), Snowflake Scripting sets the following global variables. You can use these variables to determine if the last DML statement affected any rows.

Variable

Description

SQLROWCOUNT

Number of rows affected by the last DML statement.

This is equivalent to getNumRowsAffected() in JavaScript stored procedures.

SQLFOUND

true if the last DML statement affected one or more rows.

SQLNOTFOUND

true if the last DML statement affected zero rows.

Note

The 2025_01 behavior change bundle changes the behavior of these variables. When the bundle is enabled, the variables return NULL when a non-DML statement is executed after the last DML statement in a Snowflake Scripting block or stored procedure. The bundle is enabled by default. For more information about the behavior change, see Snowflake Scripting: Changes to global variables.

If the bundle is disabled, you can enable it in your account by executing the following statement:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2025_01');
Copy

To disable the bundle, execute the following statement:

SELECT SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2025_01');
Copy

The examples in this section use the following table:

CREATE OR REPLACE TABLE my_values (value NUMBER);
Copy

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last DML statement (the INSERT statement).

BEGIN
  LET sql_row_count_var INT := 0;
  INSERT INTO my_values VALUES (1), (2), (3);
  sql_row_count_var := SQLROWCOUNT;
  SELECT * from my_values;
  RETURN sql_row_count_var;
END;
Copy

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
BEGIN
  LET sql_row_count_var INT := 0;
  INSERT INTO my_values VALUES (1), (2), (3);
  sql_row_count_var := SQLROWCOUNT;
  SELECT * from my_values;
  RETURN sql_row_count_var;
END;
$$;
Copy
+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

The following example uses the SQLFOUND and SQLNOTFOUND variables to return the number of rows affected by the last DML statement (the UPDATE statement).

BEGIN
  LET sql_row_count_var INT := 0;
  LET sql_found_var BOOLEAN := NULL;
  LET sql_notfound_var BOOLEAN := NULL;
  IF ((SELECT MAX(value) FROM my_values) > 2) THEN
    UPDATE my_values SET value = 4 WHERE value < 3;
    sql_row_count_var := SQLROWCOUNT;
    sql_found_var := SQLFOUND;
    sql_notfound_var := SQLNOTFOUND;
  END IF;
  SELECT * from my_values;
  IF (sql_found_var = true) THEN
    RETURN 'Updated ' || sql_row_count_var || ' rows.';
  ELSEIF (sql_notfound_var = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'No DML statements executed.';
  END IF;
END;
Copy

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
BEGIN
  LET sql_row_count_var INT := 0;
  LET sql_found_var BOOLEAN := NULL;
  LET sql_notfound_var BOOLEAN := NULL;
  IF ((SELECT MAX(value) FROM my_values) > 2) THEN
    UPDATE my_values SET value = 4 WHERE value < 3;
    sql_row_count_var := SQLROWCOUNT;
    sql_found_var := SQLFOUND;
    sql_notfound_var := SQLNOTFOUND;
  END IF;
  SELECT * from my_values;
  IF (sql_found_var = true) THEN
    RETURN 'Updated ' || sql_row_count_var || ' rows.';
  ELSEIF (sql_notfound_var = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'No DML statements executed.';
  END IF;
END;
$$;
Copy

When the anonymous block runs, the SQLFOUND variable is true because the UPDATE statement updates two rows.

+-----------------+
| anonymous block |
|-----------------|
| Updated 2 rows. |
+-----------------+

Query the table to see the current values:

SELECT * FROM my_values;
Copy
+-------+
| VALUE |
|-------|
|     4 |
|     4 |
|     3 |
+-------+

Run the same anonymous block again, and the results are the following:

  • The UPDATE statement is executed because there is a value in the table that is greater than 2. That is, the IF condition is satisfied.

  • The SQLNOTFOUND variable is true because no rows are updated. The UPDATE statement doesn’t update any rows because none of the values in the table are less than 3 (specified in the WHERE clause).

The query returns the following output:

+------------------+
| anonymous block  |
|------------------|
| No rows updated. |
+------------------+

Now, update the table to set all of the values to 1:

UPDATE my_values SET value = 1;

SELECT * FROM my_values;
Copy
+-------+
| VALUE |
|-------|
|     1 |
|     1 |
|     1 |
+-------+

Run the same anonymous block again, and the UPDATE statement isn’t executed because none of the values in the table are greater than 2. That is, the IF condition isn’t satisfied, so the UPDATE statement doesn’t execute.

+-----------------------------+
| anonymous block             |
|-----------------------------|
| No DML statements executed. |
+-----------------------------+
Language: English