Count of Rows Updated
Mark Maslow
affected after issuing an update?
Rob Verschoor
test this value, first copy it into a local variable since any suqsequent
statement (like select or 'if') will reset this variables, i.e.:
decalre @rc int, @err int
update your_table
set your_column = expression
where ....conditions...
select @rc = @@rowcount, @err = @@error
... now test @rc and @err as much as you want
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (new edition!)
"The Complete Sybase ASE Quick Reference Guide" (new edition!)
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
"Mark Maslow" <NOmark...@sierraclub.orgSPAM> wrote in message
news:MPG.1fd79518e...@forums.sybase.com...
Mark Maslow
@@rowcount
Contains the number of rows affected by the last query. @@rowcount is
set to 0 by any command that does not return rows, such as an if,
update, or delete statement.
In article <456e08c6$1@forums-1-dub>,
r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY says...
Mark Maslow
@@rowcount = 0 :-(
In article <MPG.1fd7a8b4a...@forums.sybase.com>,
NOmark...@sierraclub.orgSPAM says...
Robert Densmore
Works fine.
Bob
On 29 Nov 2006 14:48:43 -0700, Mark Maslow
Mark Maslow
Doc seems misleading to me - but apparently I misread it.
In article <a4asm2l65rn43hka7...@4ax.com>,
bden...@austin.rr.ignore.com says...
Derek Asirvadem
<NOmark...@sierraclub.orgSPAM> said:
> Doc seems misleading to me - but apparently I misread it.
The manuals used to be great but since 12.0 they have deteriorated considerably
@@rowcount is reset by every SQL statement that returns rows (if zero
rows are returned, the value is zero).
@@error is reset by every SQL statement (zero if no error)
Cheers
--
Derek Asirvadem
Senior Sybase DBA / Information Architect
Copyright © 2006 Software Gems Pty Ltd
"Patient, normalise thyself"
Mark Maslow
"Derek Asirvadem" <de...@NO.SPAM.softwaregems.com.au> wrote in message
news:456e6e64@forums-1-dub...
> On 2006-11-30 12:31:20 +1100, Mark Maslow
> <NOmark...@sierraclub.orgSPAM> said:
>
>> Doc seems misleading to me - but apparently I misread it.
>
> The manuals used to be great but since 12.0 they have deteriorated
> considerably
> @@rowcount is reset by every SQL statement that returns rows (if zero rows
> are returned, the value is zero).
> @@error is reset by every SQL statement (zero if no error)
>
> Cheers
> --
> Derek Asirvadem
> Senior Sybase DBA / Information Architect
> Copyright Š 2006 Software Gems Pty Ltd
> "Patient, normalise thyself"
>
Rob Verschoor
this so that we can get it corrected?
Thanks,
Rob V.
"Mark Maslow" <NOmark...@sierraclub.orgSPAM> wrote in message
news:MPG.1fd7a8b4a...@forums.sybase.com...
Mark Maslow
r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY says...
> Well, then the docs are wrong. Could you please indicate where you found
> this so that we can get it corrected?
>
> Thanks,
>
> Rob V.
>
The doc I found this in is rather old - perhaps it's better by now.
IMHO, the explanation below is confusing, if not misleading. Why is an
if statement mentioned along with update and delete?
Anyway, Derek Asivadem's explanation cleared it up perfectly for me.
Thanks.
Reference Manual
Adaptive Server Enterprise
Version 12.5
Document ID: 36271-01-1250-01
Last Revised: June 2001
Table 23-4: Global variables containing session options
A.M.
>
> Table 23-4: Global variables containing session options
>
> @@rowcount
> Contains the number of rows affected by the last query. @@rowcount is
> set to 0 by any command that does not return rows, such as an if,
> update, or delete statement.
Strictly speaking, @@rowcount is set by any DML statement.
Strictly speaking, updates and deletes do not return rows.
They are DML statements that may affect rows, however.
-am © MMVI
Mark Maslow
1. @@rowcount is set to 0 by any command that does not return rows
and, as both you and the doc say:
2. an update command does not return rows.
then it follows that:
@@rowcount will be set to zero by an update command
But, actually, @@rowcount will be set to the number of rows affected by an
update command.
Well, the last statement is really the one I care about. Damn if I can
figure out how you get there though.
"A.M." <amfo...@gmail.com> wrote in message
news:456FBF78...@gmail.com...
A.M.
>
> Well now you have confused me again. If, as doc says:
>
> 1. @@rowcount is set to 0 by any command that does not return rows
>
> and, as both you and the doc say:
>
> 2. an update command does not return rows.
>
> then it follows that:
>
> @@rowcount will be set to zero by an update command
Its because you misunderstand what's going on. @@rowcount
is set to the number of rows affected by a DML statement.
Its not restricted to only the count of the number of rows
returned by a select statement.
> But, actually, @@rowcount will be set to the number of rows affected by an
> update command.
That's correct.
> Well, the last statement is really the one I care about. Damn if I can
> figure out how you get there though.
You were already shown how to get there. @@rowcount is reset
by the next command in a batch, so you need to select it
immediately after a DML statement to see how many rows were
affected.
-am © MMVI
Mark Maslow
confusing, if not down right misleading. But apparently others think the
doc describes it perfectly. Anyway, I get it now.
"A.M." <amfo...@gmail.com> wrote in message
news:456FF6B8...@gmail.com...
Derek Asirvadem
> Its because you misunderstand what's going on. @@rowcount
> is set to the number of rows affected by a DML statement.
> Its not restricted to only the count of the number of rows
> returned by a select statement.
That is a bit unfair. The post-12.0 manuals are very confusing.
Sybase used someone who was focussed on exceptions and contradictions.
They are a dis-service to the inexperienced reader (who is of course
the person most likely to need a manual). Mark understood a simple
clear definition perfectly.
>> Well, the last statement is really the one I care about. Damn if I can
>> figure out how you get there though.
>
> You were already shown how to get there. @@rowcount is reset
> by the next command in a batch, so you need to select it
> immediately after a DML statement to see how many rows were
> affected.
He is not arguing about how to get the value, he is arguing about what
some pseudo-IT type with spiky hair and no degree said in the manual.
>> Damn if I can figure out how you get there though.
I am with you. Take a walk with Cheney's daughter and get an Advanced
Diploma in contradiction of authority figures (the pre-12.0 content of
the manuals that the Sybase Engineers wrote).
Mark Maslow
I was hung up by the elementary logic classes I took long ago where you
do the following kind of reasoning:
1. All dogs are animals.
2. Fido is a dog.
3. Therefore, Fido is an animal.
In the future, I'll keep in mind that using this kind of logic may lead
you astray when reading the Sybase ASE manuals.
Of course, real developers know better than to read manuals anyway ;-)
In article <457d05e7@forums-1-dub>, de...@NO.SPAM.softwaregems.com.au
says...
Derek Asirvadem
<NOmark...@sierraclub.orgSPAM> said:
> Indeed ...
>
> I was hung up by the elementary logic classes I took long ago where you
> do the following kind of reasoning:
>
> 1. All dogs are animals.
> 2. Fido is a dog.
> 3. Therefore, Fido is an animal.
>
> In the future, I'll keep in mind that using this kind of logic may lead
> you astray when reading the Sybase ASE manuals.
>
> Of course, real developers know better than to read manuals anyway ;-)
Don't write off the manuals ...
Actually it isn't that bad. Good and bad are relative (and subjective) terms:
- compared to the competition, the manuals are quite Good
- compared to 11.9 which were Good in the sense that they were Correct
(I submitted very few corrections) and we did not have this nonsense
(subject of this thread), the 12.2 manuals are Bad.
The problem is, Sybase actually tried to improve the manuals between
11.9 and 12.0. It is common now to use so-called technical writers to
write doco, unfortunately they do not appear to be either technical
(precise, logical) or writers (take resposibility for producing an
integrated whole reference [at least on a item basis]). The quality of
the people was not comparable to that of the engineers, so you get the
original text (from Engineering, Good) plus "Warnings!" and "Notes!"
about exceptions, which actually do not apply; confuse the issue or the
reader; or are just plain contradictory. Relative to the reference,
these are less important. There are so many that I do not bother
sending the corrections in (I still send corrections to the reference
portion in). Therefore, treat the manuals as reference material (not
education), read the reference text, but watch for and ignore the
nonsense laid on top of it.
But I agree, in the case of @@rowcount, these contradictory types had
contaminated the reference text, it was simply wrong.