Use @@RowCount for multiple updates

Question

Normally I have usp_sp_Name_InsertOrUpdate stored procedures

So my structure is simple as:

UPDATE [TableName]
     SET [Column] = @NewValue
     WHERE ...


    IF @@ROWCOUNT = 0
    BEGIN
      INSERT INTO [TableName] ...
    END

To resume this, stored procedure looks for an update, if it does not update anything @@ROWCOUNT == 0, so it proceed to INSERT statement if @ROWCOUNT > 0 just stop there.

My question is. If I have more than one UPDATE statement like:

UPDATE [TableName]
         SET [Column] = @NewValue
         WHERE ...


    UPDATE [AnotherTableName]
         SET [Column] = @NewValue
         WHERE ...

If first update returns @@ROWCOUNT > 0 but second one return 0 stored procedure continue with INSERT statement because @@ROWCOUNT only detects last UPDATE query.

Is there some trick to know if any update return @@ROWCOUNT > 0 if it isn't the last one?

Answer 1

You need something like this

DECLARE @OLD_ROWCOUNT int; 

UPDATE [TableName]
         SET [Column] = @NewValue
         WHERE ...

SET @OLD_ROWCOUNT = @@ROWCOUNT  -- capture after the first UPDATE

    UPDATE [AnotherTableName]
         SET [Column] = @NewValue
         WHERE ...

-- now you could use @@OLD_ROWCOUNT