57

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?

Sam
  • 7,252
  • 16
  • 46
  • 65
Unsliced
  • 10,404
  • 8
  • 51
  • 81

7 Answers7

59

Have you tried SET NOCOUNT OFF?

Sam
  • 7,252
  • 16
  • 46
  • 65
wcm
  • 9,045
  • 7
  • 39
  • 64
  • I had thought that I had tried this - but apparently I hadn't because it worked like a charm - thanks. – Unsliced Oct 06 '08 at 13:07
  • 1
    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)? – noelicus Jan 19 '15 at 14:20
  • *** 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 – wcm Jan 22 '15 at 18:41
11

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').

Simon
  • 6,062
  • 13
  • 60
  • 97
8

Just do this: 

SET NOCOUNT off ;
SELECT @p1 = @@ROWCOUNT

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

Adly
  • 597
  • 3
  • 12
  • 23
6

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.

Ilya Kochetov
  • 17,988
  • 6
  • 44
  • 60
1

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.

James L.
  • 9,384
  • 5
  • 38
  • 77
0

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?

Howard Pinsley
  • 11,300
  • 15
  • 49
  • 66
  • 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. – Unsliced Oct 09 '08 at 11:32
-1

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)

Corey Trager
  • 22,649
  • 18
  • 83
  • 121