How to get the affected rows (processed rows) from an update Query?
IMMEDIATLY after statement
In Oracle use: SQL%ROWCOUNT
In SQL Server use: @@RowCount
When doing Synchronization of tables this is very useful!!!
INSERT INTO xxx VALUES SELECT FROM yyy EXCEPTION WHEN OTHERS UPDATE xxx SELECT FROM yyyBad Example - catch Exceptions, do something on Exception that is expected (and happens very often)
CREATE OR REPLACE PROCEDURE sample IS v_rows_processed integer := 0; BEGIN UPDATE sample SET testno = 1; WHERE test = 'PL/SQL'; v_rows_processed := SQL%ROWCOUNT; IF v_rows_processed := 0 THEN /* Insert Statement */ END IF; END sample;.Good example - Use processed rowcount in that synch case
thx to my co-worker PK. ps: In Sql-Server @@RowCount works even with SET NOCOUNT OFF. SET NOCOUNT ON says that SQl-Server should print the messages: "rows affected" and "rows returned"