SQL Server ROWCOUNT() Function

In SQL Server, @@ROWCOUNT is a system variable used to return the number of rows affected by the last executed Transact-SQL statement. It can be used in stored procedures and triggers, as well as in client applications.

Syntax

@@ROWCOUNT does not require any parameters or parameter lists. It returns an integer value that represents the number of rows affected by the last SQL statement.

Use cases

@@ROWCOUNT is primarily used in the following situations:

• To retrieve the number of rows affected by the last SQL statement in a stored procedure or trigger.
• To retrieve the number of rows affected by the last SQL statement in a client application.

Examples

Example 1: Using @@ROWCOUNT in a stored procedure

Suppose we have a stored procedure that updates data in a table named Customers. The following is an example code for the stored procedure:

CREATE PROCEDURE UpdateCustomer
   @FirstName nvarchar(50),
   @LastName nvarchar(50),
   @Email nvarchar(50),
   @CustomerId int
AS
BEGIN
   UPDATE Customers
   SET FirstName = @FirstName,
       LastName = @LastName,
       Email = @Email
   WHERE CustomerId = @CustomerId

   SELECT @@ROWCOUNT AS 'Rows Updated'
END

The stored procedure uses an UPDATE statement to update data in the Customers table, and uses a SELECT statement to return the number of affected rows. After the stored procedure is executed, the value returned by @@ROWCOUNT will be printed to the result set.

Example 2: Using @@ROWCOUNT in a client application

Suppose we have a C# application that inserts new data into a table named Customers. The following is an example code for the application:

using System.Data.SqlClient;

string connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=True";
string insertQuery = "INSERT INTO Customers (FirstName, LastName, Email) VALUES ('John', 'Doe', '[email protected]')";

using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();

   using (SqlCommand command = new SqlCommand(insertQuery, connection))
   {
      int rowsAffected = command.ExecuteNonQuery();

      Console.WriteLine("Rows inserted: " + rowsAffected);
      Console.WriteLine("Rows affected: " + command.Parameters["@rowsAffected"].Value);
   }
}

The application uses an INSERT statement to insert new data into the Customers table, and uses the ExecuteNonQuery() method to execute the statement. After execution, the value returned by @@ROWCOUNT can be obtained by checking the @rowsAffected parameter in the SqlCommand object’s Parameters collection.

Conclusion

@@ROWCOUNT is a very useful system variable that can be used in stored procedures, triggers, and client applications to retrieve the number of rows affected by the last SQL statement.