计算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屋！