What is @@RowCount

@@RowCount returns the number of row/s affected by last statment. Let's see how it is used in real time.
Let's say that we want to insert new records from dbo.source table to our dbo.destination table. if IDs do not exist in destination table then insert otherwise update the record. To perform this we will write stored procedure.

We want to insert the row counts for updated and inserted records in dbo.Audit table in Stored procedure too for audit process.

USE TestDB
GO

--> Prepare dbo.Source Table

CREATE TABLE dbo.Source
  (
     ID   INT,
     Name VARCHAR(100)
  )

GO

--> Insert couple of records in dbo.Source Table

INSERT INTO dbo.Source 

VALUES      (1,
             'Aamir'),
            (2,
             'Raza')

GO

-->Create dbo.Destination Table.

CREATE TABLE dbo.Destination
  (
     ID   INT,
     Name VARCHAR(100)
  )

GO

--> Insert only one records in dbo.Destination table

INSERT INTO dbo.Destination

VALUES      (1,
             'Aamir Shahzad')

GO

--> Create dbo.Aduit table that will hold SourceTable,DestinationTable,updatedRowCnt,InsertRowCnt

CREATE TABLE dbo.Audit
  (
     id                   INT IDENTITY(1, 1),
     SourceTableName      VARCHAR(100),
     DestinationTableName VARCHAR(100),
     UpdatedRecords       INT,
     InsertedRecords      INT,
     LoadDate DATETIME DEFAULT GETDATE()
  )
GO

--> Create Stored Procedure for Insert/Update

CREATE PROCEDURE Dbo.Updatedestination

AS
  BEGIN
      DECLARE @UpdatedRecords INT
      DECLARE @InsertedRecprds INT

      -->Update Exisiting Records
      UPDATE DST
      SET    DST.Name = SRC.Name
      FROM   dbo.Destination DST
             INNER JOIN DBO.Source SRC
                     ON DST.ID = SRC.ID

--SET the values of @updatedRecords variable by using @@RowCount
      SET @UpdatedRecords=@@ROWCOUNT

      -->Insert New Records
      INSERT INTO dbo.Destination
                  (ID,
                   Name)
      SELECT ID,
             Name
      FROM   dbo.Source S
      WHERE  NOT EXISTS (SELECT 1
                         FROM   dbo.Destination D
                         WHERE  S.ID = D.ID)

--> Set the value of @InsertedRecords variable by using @@ROWCOUNT
      SET @InsertedRecprds=@@ROWCOUNT

      --> Insert Row Counts into dbo.Audit Table
      INSERT INTO dbo.Audit
                  (SourceTableName,
                   DestinationTableName,
                   InsertedRecords,
                   UpdatedRecords)
      SELECT 'dbo.Source',
             'dbo.Destination',
             @InsertedRecprds,
             @UpdatedRecords
  END

-->Execute Stored Procedure couple of times

EXEC dbo.Updatedestination

--> Check the Audit Table

SELECT * FROM dbo.Audit
--Drop All object which created for this scenario
--drop table dbo.Source
--drop table dbo.Destination
--drop table dbo.Audit
--drop procedure dbo.Updatedestination

Fig 1: Records affected