Rows Affected by Last Snowflake SQL Query

Many modern databases such as Teradata, SQL Server, etc. support system variables to get rows affected by the last DML command. Then the row count is used to make certain decision to continue execution or abort it. Snowflake scripting provides system variables that hold the information of records affected by an INSERT, UPDATE or DELETE queries in the current session. In this article, we will check how to get rows affected by last Snowflake SQL Query.

After each DML command is executed, Snowflake Scripting sets the certain global variables. You can use these following variables to determine if the last SQL statement affected any rows.

Variable Name	Description
`SQLROWCOUNT`	Returns number of rows affected by the last DML statement.
`SQLFOUND`	Returns `true` if the last DML statement affected one or more rows.
`SQLNOTFOUND`	Returns `true` if the last DML statement affected zero rows.
`getNumRowsAffected()`	Returns number of rows affected by the last DML statement in JavaScript stored procedures. This is equivalent to `SQLROWCOUNT` in Snowflake Scripting.

Example to Get Rows Affected by Last Snowflake INSERT Query

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

execute immediate $$
begin
  -- Insert 3 rows into a table.
  insert into test_table values (1), (2), (3);
  
  -- Returns the number of rows affected by the last INSERT statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 3 as an output.

Example to Get Rows Affected by Last Snowflake UPDATE Query

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last UPDATE statement.

execute immediate $$
begin
  UPDATE test_table
  SET col1 = 1
  WHERE col1 = 3;
  
  -- Returns the number of rows affected by the last UPDATE statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 1 as an output.

Example to Get Rows Affected by Last Snowflake DELETE Query

The following example uses the SQLROWCOUNT variable to return the number of rows affected by the last DELETE statement.

execute immediate $$
begin
  DELETE FROM test_table
  WHERE col1 = 1;
  
  -- Returns the number of rows affected by the last DELETE statement.
  return sqlrowcount;
end;
$$;

Above anonymous block returns 2 as an output.

Example to Get Rows Affected by Last Snowflake UPDATE Query in Snowflake JavaScript Procedure

The following Snowflake JavaScript stored procedure uses the getNumRowsAffected() JavaScript method to return the number of rows affected by the last UPDATE statement.

create or replace procedure stproc1()
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
        var rs = snowflake.execute (
            {sqlText: "UPDATE test_table SET col1 = 1 WHERE col1 = 1"}
            );
        return rs.getNumRowsAffected()
    $$
    ;

Above Snowflake JavaScript stored procedure returns 1 as an output.