How can I get the number of records affected by a stored procedure?

Using out parameters to your advantage

Out parameters can help you package the results, allowing the direct retrieval of the affected rows count. Define an out parameter in your stored procedure to hold the row count.

CREATE PROCEDURE YourStoredProc
    @RowsAffected INT OUTPUT
AS
BEGIN
    -- Your SQL Statements - making magic happen
    SET @RowsAffected = @@ROWCOUNT;
END


Upon calling the procedure, you can get this value like so:

DECLARE @RowCount INT;
EXEC YourStoredProc @RowsAffected = @RowCount OUTPUT;
SELECT @RowCount as RowsAffected; -- Ta-dah! Here are your affected rows.


For the multi-taskers: Handling multiple operations

Accumulating row counts with local variables

If your stored procedure contains multiple statements, make sure to accumulate counts to prevent overwriting the value. Steal @@ROWCOUNT after each DML statement.

DECLARE @TotalRowCount INT = 0;
-- First SQL operation - SQL magic
SET @TotalRowCount = @TotalRowCount + @@ROWCOUNT;
-- Second SQL operation - More SQL magic
SET @TotalRowCount = @TotalRowCount + @@ROWCOUNT;
-- etc. - SQL wizardry continues...


This ensures that you're keeping tabs on all the rows dabbled by your Hogwarts-approved spells.

Marrying error handling with row counts

Incorporate both @@ERROR and @@ROWCOUNT in your error-handling strategy. Snag them immediately after your SQL statement to prevent them from being meddled with by other commands.

DECLARE @ErrorStatus INT, @AffectedRows INT;
-- Perform SQL operation - Just doing some SQL things
SET @ErrorStatus = @@ERROR;
SET @AffectedRows = @@ROWCOUNT;

IF @ErrorStatus != 0
BEGIN
    -- Handle error - Oops! Houston, we’ve had a problem
END


SQL Server quirks

Make sure SET NOCOUNT is OFF to ensure the wizardly response of the SqlCommand.ExecuteNonQuery(). Failure to do so might pull a rabbit out of the hat: a misleading return of -1.

SET NOCOUNT OFF; -- Now we're talking!


Documenting performance in Oracle

Oracle users - join the party with SQL%ROWCOUNT!

-- After executing DML statement in the procedure
affected_rows := SQL%ROWCOUNT; -- This is Oracle country!


Dodgeball with SqlCommand

Pivot SqlCommand.ExecuteNonQuery() to bring you the correct count of affected rows. Be cautious - a recall on the transaction can netBack to square one!

EXEC YourStoredProc
-- SqlCommand realizes it made a mistake and tries to come back: Nope, sorry! 


From SQL Apprentice to SQL Master

• Store results in out parameters after each operation within a transaction - They can duck out anytime!
• For critical operations, box your row counts with transaction scopes - Because safety is fancy!
• Log your row counts in tables for those mystery hunting, coffee chugging debug nights.