Demystifying @@ROWCOUNT: Your Guide to Tracking Deletions in Stored Procedures

Here are two common approaches to count the number of deleted rows within a SQL Server stored procedure:

Using @@ROWCOUNT:

• T-SQL provides a system variable named @@ROWCOUNT that stores the number of rows affected by the most recently executed Data Manipulation Language (DML) statement, including DELETE.
• After the DELETE statement within your stored procedure, you can access the @@ROWCOUNT variable to retrieve the deleted row count.

Here's an example:

CREATE PROCEDURE DeleteCustomers 
(
  @CustomerID INT
)
AS
BEGIN
  DELETE FROM Customers WHERE CustomerID = @CustomerID;

  SELECT 'Number of deleted rows: ' + CAST(@@ROWCOUNT AS VARCHAR(10));
END;

In this example, the stored procedure DeleteCustomers takes a CustomerID as input and deletes the corresponding row from the Customers table. After the deletion, it retrieves the @@ROWCOUNT value and displays it as a message.

Using OUTPUT Clause:

• The OUTPUT clause allows you to capture information from the deleted rows and return it as part of the result set.
• You can use the DELETED.* alias to access all columns from the deleted rows and then use a COUNT(*) function to get the total count.

CREATE PROCEDURE DeleteCustomers 
(
  @CustomerID INT,
  @DeletedCount INT OUTPUT
)
AS
BEGIN
  DELETE FROM Customers 
  OUTPUT DELETED.* INTO @DeletedCount;

  SELECT 'Number of deleted rows: ' + CAST(@DeletedCount AS VARCHAR(10));
END;

This example is similar to the previous one, but it uses the OUTPUT clause to capture the deleted rows into an output parameter @DeletedCount. Then, it uses this parameter to display the count.

Choosing the Right Method:

• If you only need the total number of deleted rows and don't need access to specific information from the deleted rows, @@ROWCOUNT is a simpler and more efficient option.
• If you require details about the deleted rows (e.g., for logging purposes), the OUTPUT clause provides a more comprehensive solution.

Additional Considerations:

• Remember that @@ROWCOUNT is a session-level variable and can be affected by other DML statements executed in the same session.
• Be cautious when using @@ROWCOUNT within loops or transactions, as its value might not reflect the specific deletion you're interested in.