Alex Rivera | Logout

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

Asked 2008-10-06T12:56:22.690
59

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?

sql-server t-sql audit sql-delete
Edit
Report

1 Answer

7

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.

answered 2008-10-06T13:03:56.480

Your Answer