February 21, 2013 at 3:42 pm
Hi All,
I've been racking my brain and haven't remembered the answer to this. Google isn't helping either. I seem to recall that there is an alternative to @@RowCount that can be used in a transaction to return the row count for that transaction. Am I crazy or just not looking in the right place?
Example:
Begin tran 1
Begin tran 2
<do stuff>
commit tran 1
commit tran 2 (finishes before @@RowCount can be executed for tran 1)
select @@RowCount (this gives me the row count for tran 2, when I want the count for tran 1)
TIA,
Rob
February 22, 2013 at 2:20 am
and what do you get if you run the one below:
Begin tran
select 10
select @@RowCount
Begin tran
select 50
select @@RowCount -- 1
commit tran
select 100
select @@RowCount -- 1
commit tran
select @@RowCount -- 0
select @@TRANCOUNT
February 22, 2013 at 11:39 am
In SQL Server this is really only one transaction. You can issue multiple BEGIN TRANSACTIONS, but the outer transaction is the only one that really counts. You can use "savepoints" within trans, but not a true separate transaction.
As to rowcounts for a transaction, I don't know of anything like that. What would the total include: all rows SELECTed, INSERTed, UPDATEd or DELETEd? I/U/Ds done in triggers? In called stored procs??
AFAIK, you need to add the @@ROWCOUNT after each statement yourself to get a total for a transaction / block of code.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 22, 2013 at 6:25 pm
@@ROWCOUNT is overwitten after each statement is executed (its scope is therefore lost). Create a variable before the transaction declarations and assign the @@ROWCOUNT to it for each statement executed inside each transaction.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply