Determining the number of rows affected by DML commands (original) (raw)

After a DML command is executed (excluding the TRUNCATE TABLEcommand), Snowflake Scripting sets the following global variables. You can use these variables to determine if the last DML statement affected any 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');

To disable the bundle, execute the following statement:

SELECT SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2025_01');

The examples in this section use the following table:

CREATE OR REPLACE TABLE my_values (value NUMBER);

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;

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

EXECUTE IMMEDIATE BEGINLETsqlrowcountvarINT:=0;INSERTINTOmyvaluesVALUES(1),(2),(3);sqlrowcountvar:=SQLROWCOUNT;SELECT∗frommyvalues;RETURNsqlrowcountvar;END;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;BEGINLETsqlr​owc​ountv​arINT:=0;INSERTINTOmyv​aluesVALUES(1),(2),(3);sqlr​owc​ountv​ar:=SQLROWCOUNT;SELECT∗frommyv​alues;RETURNsqlr​owc​ountv​ar;END;;

+-----------------+

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;

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

EXECUTE IMMEDIATE BEGINLETsqlrowcountvarINT:=0;LETsqlfoundvarBOOLEAN:=NULL;LETsqlnotfoundvarBOOLEAN:=NULL;IF((SELECTMAX(value)FROMmyvalues)>2)THENUPDATEmyvaluesSETvalue=4WHEREvalue<3;sqlrowcountvar:=SQLROWCOUNT;sqlfoundvar:=SQLFOUND;sqlnotfoundvar:=SQLNOTFOUND;ENDIF;SELECT∗frommyvalues;IF(sqlfoundvar=true)THENRETURN′Updated′∣∣sqlrowcountvar∣∣′rows.′;ELSEIF(sqlnotfoundvar=true)THENRETURN′Norowsupdated.′;ELSERETURN′NoDMLstatementsexecuted.′;ENDIF;END;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;BEGINLETsqlr​owc​ountv​arINT:=0;LETsqlf​oundv​arBOOLEAN:=NULL;LETsqln​otfoundv​arBOOLEAN:=NULL;IF((SELECTMAX(value)FROMmyv​alues)>2)THENUPDATEmyv​aluesSETvalue=4WHEREvalue<3;sqlr​owc​ountv​ar:=SQLROWCOUNT;sqlf​oundv​ar:=SQLFOUND;sqln​otfoundv​ar:=SQLNOTFOUND;ENDIF;SELECT∗frommyv​alues;IF(sqlf​oundv​ar=true)THENRETURN′Updated′∣∣sqlr​owc​ountv​ar∣∣′rows.′;ELSEIF(sqln​otfoundv​ar=true)THENRETURN′Norowsupdated.′;ELSERETURN′NoDMLstatementsexecuted.′;ENDIF;END;;

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

+-----------------+

Query the table to see the current values:

+-------+

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:

+------------------+

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

UPDATE my_values SET value = 1;

SELECT * FROM my_values;

+-------+

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.

+-----------------------------+