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 |
---|---|
|
Number of rows affected by the last DML statement. This is equivalent to |
|
|
|
|
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. |
+-----------------+