计算 SQL Server 存储过程中删除的行数 [英] Counting the number of deleted rows in a SQL Server stored procedure

问题描述

在 SQL Server 2005 中，是否可以删除行并被告知实际删除了多少行?

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

我可以使用相同的条件执行 select count(*)，但我需要它完全值得信赖.

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

我的第一个猜测是使用 @@ROWCOUNT 变量 - 但没有设置，例如

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 

总是返回 0.

MSDN 建议使用 OUTPUT 构造，例如

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.

有什么想法吗?

推荐答案

你试过 SET NOCOUNT OFF 吗?

这篇关于计算 SQL Server 存储过程中删除的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！