@@ERROR and @@ROWCOUNT

Since @.@.ERROR is cleared by the very next statement and @.@.ROWCOUNT is cleare
d by the very next statement, how do you save the @.@.ROWCOUNT and then check
the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT fro
m the SELECT statement just executed...Try:
declare
@.ERROR int
, @.ROWCOUNT int
select
@.ERROR = @.@.ERROR
, @.ROWCOUNT = @.@.ROWCOUNT
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:684F45E8-4501-4A45-9FE9-A3A5AA9FCB81@.microsoft.com...
Since @.@.ERROR is cleared by the very next statement and @.@.ROWCOUNT is
cleared by the very next statement, how do you save the @.@.ROWCOUNT and then
check the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT
from the SELECT statement just executed...|||Thanks - that just dawned on me as well. Brain freeze - shouldn't develop
and talk to the customers at the same time...
I'm so set against using SELECT to set variable values - is there a justific
ation for that feeling I have, or did I dream it up...|||Steve Z wrote:

> I'm so set against using SELECT to set variable values - is there a justification
for that feeling I have, or did I dream it up...
I have the same phobia and usually always find myself using SET. The
only time I use SELECT is when I want something from a table.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||http://www.tkdinesh.com/faq/ans/setorselect.html
No performance penalty or anything like that, but it does violate the
standard (perhaps important if you want to write code that's as portable as
possible).
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:AA0BCA4F-61A1-4C32-8D4E-DB100850EB70@.microsoft.com...
> Thanks - that just dawned on me as well. Brain freeze - shouldn't
develop and talk to the customers at the same time...
> I'm so set against using SELECT to set variable values - is there a
justification for that feeling I have, or did I dream it up...
>|||So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I agre
e and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and complicat
ed and hard to debug)...|||Personally, I prefer SET as I find it easier to read (primary reason) and it
is also ANSI SQL compliant. The one exception is when I have to or really
want to do several assignments in one statement. Your @.@.ERROR and @.@.ROWCOUNT
is one example. Below is another:
SELECT @.fname = au_fname, @.lname = au_lname, @.whatever = whatever
FROM tbl
WHERE pkcol = @.pk
Note for above, if more than one row is returned (bug in design) you will
not get an error, the variables will contain values from the last row
processed. Also, if no row is returned, you the variables will go unchanged.
I know of other programmers who prefer to be consistent, and because of that
always use SELECT for all variable assignments. I'm not one of them, but I
respect that viewpoint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
> So, to beat a dead horse here...
> You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
> But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of
those values at the same time, you must use SELECT.
> The higher generation language you go, the looser the rules - I remember
the old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||>> But in the case of @.@.ERROR and @.@.ROWCOUNT,
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||Vyas,
That you for the interesting article and that neat trick to save both values
at once.
I think, though, in the few cases where the @.@.ROWCOUNT and @.@.ERROR values ar
e important to me, I will use SELECT to save them both.
Steve
-- Narayana Vyas Kondreddi wrote: --
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...