Menu
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?
294
As in, SET v_count = @@ROWCOUNT. below is the pl-sql code. It, displays the number of total records inserted/updated/deleted. If the count matches, will issue COMMIT otherwise rollback.
Use SQL%ROWCOUNT if you are using Oracle. Mind that if you have multiple INSERT/UPDATE/DELETE , you'll need a variable to store the result from @@ROWCOUNT for each operation. Show activity on this post. @@RowCount will give you the number of records affected by a SQL Statement.
Usage. SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch.
Have you tried SET NOCOUNT OFF?
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
