Counting the number of deleted rows in a SQL Server stored procedure

Question

In SQL Server 2005, is there a way of deleting rows and being told how many were actually deleted?

I could do a select count(*) with the same conditions, but I need this to be utterly trustworthy.

My first guess was to use the @@ROWCOUNT variables - but that isn't set, e.g.

delete 
from mytable 
where datefield = '5-Oct-2008' 

select @@ROWCOUNT 

always returns a 0.

MSDN suggests the OUTPUT construction, e.g.

delete from mytable 
where datefield = '5-Oct-2008' 
output datefield into #doomed

select count(*) 
from #doomed

this actually fails with a syntax error.

Any ideas?

Fingerstall

Answer 1

Have you tried SET NOCOUNT OFF?

Madden

I had thought that I had tried this - but apparently I hadn't because it worked like a charm - thanks. – Fingerstall

MSDN seems to say the SET NOCOUNT effects the print-out. Why does this affect the @@ROWCOUNT? Shouldn't it always return the number of rows deleted (in the OP)? – Lavettelavigne

*** This is conjecture *** I think it's because setting NOCOUNT on tells the system that it doesn't have to keep track of counts in order to make things more efficient – Madden

Answer 2

I use @@ROWCOUNT for this exact purpose in SQL2000 with no issues. Make sure that you're not inadvertantly resetting this count before checking it though (BOL: 'This variable is set to 0 by any statement that does not return rows, such as an IF statement').

Vercingetorix

Answer 3

Just do this:

SET NOCOUNT off ;
SELECT @p1 = @@ROWCOUNT

where p1 is the output parameter you set in the stored procedure. Hope it helps.

Ripe

Answer 4

In your example @@ROWCOUNT should work - it's a proper way to find out a number of deleted rows. If you're trying to delete something from your application then you'll need to use SET NOCOUNT ON

According to MSDN @@ROWCOUNT function is updated even when SET NOCOUNT is ON as SET NOCOUNT only affects the message you get after the the execution.

So if you're trying to work with the results of @@ROWCOUNT from, for example, ADO.NET then SET NOCOUNT ON should definitely help.

Cherub

? This is the exact opposite of the accepted answer. – Thithia

Answer 5

I found a case where you can't use @@rowcount, like when you want to know the distinct count of the values that were deleted instead of the total count. In this case you would have to do the following:

delete from mytable 
where datefield = '5-Oct-2008' 
output deleted.datefield into #doomed

select count(distinct datefield)
from #doomed

The syntax error in the OP was because output did not include deleted before the datefield field name.

Vanwinkle

Answer 6

Create temp table with one column, id.

Insert into temp table selecting the ids you want to delete. That gives you your count.

Delete from your table where id in (select id from temp table)

Unprecedented

Although something that would work, the overhead of something like this is a waste. – Dniren

This would give a bad performance indeed, query would take at least 2 times longer, which could be a considerable time if you're handling lots of records. Creative thinking though! – Password

Answer 7

Out of curiosity, how are you calling the procedure? (I'm assuming it is a stored procedure?). The reason I ask is that there is a difference between a stored procedure's return value (which would be 0 in this case), and a rowset result -- which in this case would be a single row with a single column. In ADO.Net, the former would be accessed by a parameter and the latter with a SqlDataReader. Are you, perhaps, mistaking the procedure's return value as the rowcount?

Knockwurst

This was code all in one place (but yes, in a stored procedure) - I was deleting some data then writing that fact to a status table, so it wasn't really a case of being confused over return values. – Fingerstall

Answer 8

I suggest to use

SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT

when trying to get @@error AND @@ROWCOUNT Because reading @@error will reset @@ROWCOUNT, event PRINT statement will reset the @@ROWCOUNT

There example on learn.microsoft.com about this.

https://learn.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver16

Burgonet