How can I get number of deleted records?

That should work fine. The setting of NOCOUNT is irrelevant. This only affects the n rows affected information sent back to the client and has no effect on the workings of @@ROWCOUNT.

Do you have any statements between the two that you have shown? @@ROWCOUNT is reset after every statement so you must retrieve the value immediately with no intervening statements.


I use this code snippet when debugging stored procedures to verify counts after operations, such as a delete:

DECLARE @Msg varchar(30)
...
SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
RAISERROR (@Msg, 0, 1) WITH NOWAIT

Tags:

Sql Server