Counting deleted rows in a SQL Server stored procedure

Question

In SQL Server 2005, is there a way to delete rows and find out how many of them are really deleted?

I could do select count(*) with the same conditions, but I need this to be absolutely reliable.

My first assumption was to use @@ROWCOUNT variables, but this is not set, for example.

 delete from mytable where datefield = '5-Oct-2008' select @@ROWCOUNT 

always returns 0.

MSDN offers an OUTPUT construct, for example.

 delete from mytable where datefield = '5-Oct-2008' output datefield into #doomed select count(*) from #doomed 

it really is not with a syntax error.

Any ideas?

Answer 1

Have you tried SET NOCOUNT OFF ?

Answer 2

I use @@ ROWCOUNT for this specific purpose in SQL2000 without any problems. Make sure that you do not accidentally reset this account before checking it (BOL: "This variable is set to 0 by any statement that does not return rows, such as the IF statement").

Answer 3

Just do the following:

 SET NOCOUNT off ; SELECT @p1 = @@ROWCOUNT 

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

Answer 4

In your example, @@ROWCOUNT should work - this is the right way to find out the number of rows deleted. If you are trying to remove something from your application, you need to use SET NOCOUNT ON

According to MSDN @@, the ROWCOUNT function is updated even if SET NOCOUNT is enabled, since SET NOCOUNT only affects the message that you receive after execution.

So, if you are trying to work with @@ROWCOUNT , for example, using ADO.NET, then SET NOCOUNT ON should definitely help.

Answer 5

I found a case where you cannot use @@rowcount , for example, when you want to know a different number of values ​​that were deleted instead of the total score. In this case, you will need 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 that output did not include deleted before the datefield field datefield .

Answer 6

Create a temporary table with one column, id.

Insert temp into the table by selecting the identifiers you want to delete. It gives you your score.

Delete from the table where id (select id from temp table)

Answer 7

Out of curiosity, what do you call this procedure? (I assume this is a stored procedure?). The reason I'm asking is because there is a difference between the value of the returned stored procedure (which in this case will be 0) and the result of the rowset, which in this case will be one row with one column. In ADO.Net, the first of them will be available by parameter, and the last - using SqlDataReader. Perhaps you are mistaken in returning the value of the procedure as a string?