Quandary: System Variables Resetting

[NeoPa]

I'm looking to use values set for both @@ERROR and @@ROWCOUNT after a query is run.

Unfortunately, it seems that any SQL Statement resets @@ERROR, and any SET statement resets @@ROWCOUNT.

How then, do I save the values of both at the point immediately after the query has been run?

I started by saving @@ERROR into a local variable using :

DECLARE @SaveErr AS int
 
DELETE FROM dbo.MyTable
 
SET @SaveErr = @@ERROR

At this point the SET line has cleared the value in @@ROWCOUNT. If I save @@ROWCOUNT first it seems I lose the value of @@ERROR.

I expect I'm approaching this all from the wrong direction, so any advice is welcomed. I'd certainly like to understand how to manage this though, even if bypassing the issue may be the preferred approach.

[code green]

This is quite a dilemma.
So accessing either system variable emptys both of them?

There is probably a correct way to do this.
In the meantime what happens when you try this?

DECLARE @BothErr AS VARCHAR
 
DELETE FROM dbo.MyTable 
 
SET @BothErr = 'Error: '+ @@ERROR + 'Rowcount '+ @@ROWCOUNT 
 

[NeoPa]

That would complain about using + with data types that have no default conversion. Using CAST or CONVERT on the int values would work. I would hope somewhere there is a solution that doesn't involve converting both items to a string though. I can't help feeling I must be approaching it in a way that an experienced T-SQL coder wouldn't. I can't explain better until I am a more experienced T-SQL coder ;)

Thanks for responding. Your efforts are appreciated :)

[Jerry Winston]

How does this work out for you:

DECLARE @errTable TABLE (eCode INT, eRow INT)
INSERT INTO @errTable (eCode,eRow) VALUES (@@ERROR, @@ROWCOUNT);
 
 

[ck9663]

Why not save the two values at the same time?

 
DECLARE @SaveErr int, @AffectedRows int
 
DELETE FROM dbo.MyTable
 
SET @SaveErr = @@ERROR, @AffectedRows = @@rowcount
 
 

Happy Coding!!!

~~ CK

**Edit**

b0010100: Should last line be?:

SELECT @SaveErr = @@ERROR, @AffectedRows = @@rowcount

ck9663: b0010100 is correct, it should've been a SELECT, not SET...

[Jerry Winston]

@CK

Should last line be?:

SELECT @SaveErr = @@ERROR, @AffectedRows = @@rowcount

[NeoPa]

ck9663: Why not save the two values at the same time?

Because I didn't know how to :D

In truth, I did explore that a bit but didn't stumble across the correct syntax. I'm buried in the books online for much of the time at the mnoment, and I'm generally quite impressed with what it can teach me, but I still find some things hard to discover there.

Thanks for this tip. I can't test it till Monday now, but frankly I know your work well enough to know that's just a formality.

Thanks also to b0010100. What you suggested may well work. I think I prefer the less involved method where possible though. I'm sure as I get more familiar with it all things will occur to me more naturally, but for now I'm just trying to get up to speed.

[ck9663]

b0010100 is correct, it should've been a SELECT, not SET...

thanks man...

~~ CK

[NeoPa]

Thanks CK. I've updated your Best Answer post to reflect that for anyone else reading this later.

Thanks again b0010100. Your input was important :)