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

发布于 2024-07-06 13:10:31 字数 618 浏览 10 评论 0原文

在 SQL Server 2005 中,是否有一种方法可以删除行并告知实际删除了多少行?

我可以在相同的条件下执行 select count(*) ,但我需要它完全值得信赖。

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

delete 
from mytable 
where datefield = '5-Oct-2008' 

select @@ROWCOUNT 

总是返回 0。MSDN

建议 OUTPUT 构造,例如,

delete from mytable 
where datefield = '5-Oct-2008' 
output datefield into #doomed

select count(*) 
from #doomed

这实际上因语法错误而失败。

有任何想法吗?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(8

客…行舟 2024-07-13 13:10:31

您是否尝试过SET NOCOUNT OFF

Have you tried SET NOCOUNT OFF?

孤独患者 2024-07-13 13:10:31

我在 SQL2000 中使用 @@ROWCOUNT 来达到这个目的,没有任何问题。 确保在检查之前没有无意中重置此计数(BOL:“此变量被任何不返回行的语句设置为 0,例如 IF 语句”)。

I use @@ROWCOUNT for this exact purpose in SQL2000 with no issues. Make sure that you're not inadvertantly resetting this count before checking it though (BOL: 'This variable is set to 0 by any statement that does not return rows, such as an IF statement').

无戏配角 2024-07-13 13:10:31

只需这样做:

SET NOCOUNT off ;
SELECT @p1 = @@ROWCOUNT

其中 p1 是您在存储过程中设置的输出参数。
希望能帮助到你。

Just do this:

SET NOCOUNT off ;
SELECT @p1 = @@ROWCOUNT

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

ゃ懵逼小萝莉 2024-07-13 13:10:31

在您的示例中 @@ROWCOUNT 应该可以工作 - 这是查找已删除行数的正确方法。 如果您尝试从应用程序中删除某些内容,则需要使用SET NOCOUNT ON

根据MSDN 即使 SET NOCOUNT 为 ON,@@ROWCOUNT 函数也会更新,因为 SET NOCOUNT 仅影响执行后收到的消息。

因此,如果您尝试使用来自 ADO.NET 等的 @@ROWCOUNT 结果,那么 SET NOCOUNT ON 肯定会有所帮助。

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.

凉薄对峙 2024-07-13 13:10:31

我发现了一种不能使用 @@rowcount 的情况,例如当您想知道已删除值的不同计数而不是总计数时。 在这种情况下,您必须执行以下操作:

delete from mytable 
where datefield = '5-Oct-2008' 
output deleted.datefield into #doomed

select count(distinct datefield)
from #doomed

OP中的语法错误是因为outputdatefield字段名称之前没有包含deleted

I found a case where you can't use @@rowcount, like when you want to know the distinct count of the values that were deleted instead of the total count. In this case you would have 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 because output did not include deleted before the datefield field name.

情愿 2024-07-13 13:10:31

创建包含一列 id 的临时表。

插入到临时表中,选择要删除的 ID。 这给了你你的计数。

从 id 所在的表中删除(从临时表中选择 id)

Create temp table with one column, id.

Insert into temp table selecting the ids you want to delete. That gives you your count.

Delete from your table where id in (select id from temp table)

不…忘初心 2024-07-13 13:10:31

出于好奇,您如何调用该过程? (我假设它是一个存储过程?)。 我问的原因是存储过程的返回值(在本例中为 0)和行集结果(在本例中为单行单列)之间存在差异。 在 ADO.Net 中,前者将通过参数访问,后者则通过 SqlDataReader 访问。 您是否可能将过程的返回值误认为是行计数?

Out of curiosity, how are you calling the procedure? (I'm assuming it is a stored procedure?). The reason I ask is that there is a difference between a stored procedure's return value (which would be 0 in this case), and a rowset result -- which in this case would be a single row with a single column. In ADO.Net, the former would be accessed by a parameter and the latter with a SqlDataReader. Are you, perhaps, mistaking the procedure's return value as the rowcount?

捶死心动 2024-07-13 13:10:31

使用

SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT

我建议在尝试获取 @@error AND @@ROWCOUNT 时
因为读取@@error会重置@@ROWCOUNT,
事件 PRINT 语句将重置 @@ROWCOUNT

learn.microsoft.com 上有关于此的示例。

https: //learn.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver16

I suggest to use

SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT

when trying to get @@error AND @@ROWCOUNT
Because reading @@error will reset @@ROWCOUNT,
event PRINT statement will reset the @@ROWCOUNT

There example on learn.microsoft.com about this.

https://learn.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver16

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文