Howto: Affected Rows in Sql-Server and Oracle?

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!!!


Synchronization PseudoCode

       INSERT INTO xxx VALUES SELECT FROM yyy
       EXCEPTION
       WHEN OTHERS
          UPDATE xxx SELECT FROM yyy

Bad 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"