计算 SQL Server 存储过程中删除的行数

2021-11-30 00:00:00 tsql sql-delete sql-server audit

在 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 吗?

相关文章