sábado, 17 de septiembre de 2011

update_recordset && delete_from - How many rows was that anyway?

We wanted to know how many rows were updated when using the update_recordset statement and at first it was a case of attacking the database twice, but with the same where clause. Thus:
LedgerTrans        tbl1;
;
select count(RecId)
from tbl1
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;

print tbl1.RecId;
pause;

update_recordset tbl1
setting EVE_Transferred = EVE_LedgerTransTransferred::ProvisionallyExcluded
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;
We would get the number of rows to be updated from the first statement. However the cleanest way to do this was using the RowCount() method, whose documentation is best described as 'spartan'.

LedgerTrans        tbl1;
;
update_recordset tbl1
setting EVE_Transferred = EVE_LedgerTransTransferred::ProvisionallyExcluded
where tbl1.EVE_Transferred == EVE_LedgerTransTransferred::PermanentlyExcluded;

print tbl1.RowCount();
pause
The trick works both for update_recordset and delete_from statements. Yet again this was a case of reading the MSDN documentation twice over to discover this one - especially the code section.

1 comentario: