Determining the Number of Rows Affected by DML Commands

After each DML command is executed, 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 getNumDuplicateRowsUpdated() 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.

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last DML statement (the INSERT statement, in this example). Note that the SELECT statement is not a DML statement and has no effect on the SQLROWCOUNT variable.

EXECUTE IMMEDIATE $$
BEGIN

  -- Insert 3 rows into a table.
  INSERT INTO my_values VALUES (1), (2), (3);
  -- SQLROWCOUNT is not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- Returns the number of rows affected by
  -- the last DML statement (the INSERT statement).
  RETURN SQLROWCOUNT;

END;
$$;
+-----------------+
| 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). As is the case with the previous example, the SELECT statement does not affect the SQLFOUND and SQLNOTFOUND variables.

EXECUTE IMMEDIATE $$
BEGIN

  -- Update the rows in a table that have values less than 3.
  UPDATE my_values SET value = 4 WHERE value < 3;
  -- SQLFOUND and SQLNOTFOUND are not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- SQLFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected one or more rows.
  IF (SQLFOUND = true) THEN
    RETURN 'Updated ' || SQLROWCOUNT || ' rows.';
  -- SQLNOTFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected zero rows.
  ELSEIF (SQLNOTFOUND = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'SQLFOUND and SQLNOTFOUND are not true.';
  END IF;

END;
$$;
+-----------------+
| anonymous block |
|-----------------|
| Updated 2 rows. |
+-----------------+
Language: English