计算 SQL Server 存储过程中删除的行数
在 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
您是否尝试过
SET NOCOUNT OFF？Have you tried
SET NOCOUNT OFF?我在 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').
只需这样做：
其中 p1 是您在存储过程中设置的输出参数。
希望能帮助到你。
Just do this:
where p1 is the output parameter you set in the stored procedure.
Hope it helps.
在您的示例中
@@ROWCOUNT应该可以工作 - 这是查找已删除行数的正确方法。 如果您尝试从应用程序中删除某些内容，则需要使用SET NOCOUNT ON根据MSDN 即使 SET NOCOUNT 为 ON，@@ROWCOUNT 函数也会更新，因为
SET NOCOUNT仅影响执行后收到的消息。因此，如果您尝试使用来自 ADO.NET 等的
@@ROWCOUNT结果，那么SET NOCOUNT ON肯定会有所帮助。In your example
@@ROWCOUNTshould 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 useSET NOCOUNT ONAccording to MSDN @@ROWCOUNT function is updated even when SET NOCOUNT is ON as
SET NOCOUNTonly affects the message you get after the the execution.So if you're trying to work with the results of
@@ROWCOUNTfrom, for example, ADO.NET thenSET NOCOUNT ONshould definitely help.我发现了一种不能使用
@@rowcount的情况，例如当您想知道已删除值的不同计数而不是总计数时。 在这种情况下，您必须执行以下操作：OP中的语法错误是因为
output在datefield字段名称之前没有包含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:The syntax error in the OP was because
outputdid not includedeletedbefore thedatefieldfield name.创建包含一列 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)
出于好奇，您如何调用该过程？ （我假设它是一个存储过程？）。 我问的原因是存储过程的返回值（在本例中为 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?
使用
我建议在尝试获取 @@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
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