select @error = @@error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select @error = @@error

Author

Topic

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-14 : 19:17:18

no matter what happens @@error and @error always yields 0. Am I doing something wrong, I'm beginning to wonder...

select @error = @@error

if @error <> 0
select @error

Derrick Walker

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-14 : 19:27:47

???
@@error will be set if there is a trapable error and reset for every statement.

create table #a(i int not null primary key)
declare @error int
insert #a select 1
select @error = @@error
select @error
insert #a select 1
select @error = @@error
select @error
insert #a select 2
select @error = @@error
select @error

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-15 : 10:23:22

So what is done about errors that cannot be trapped? For instance if I want to execute a line of code if a procedure throws an error is it best to simply get the return value 0 or 1

exec @return = my_proc....

Currently I am evaluating the return value though I'm sure if there is some other way that will work better.

declare @return tinyint,
@error int

EXEC my_proc 'values'

select @error = @@error
select @error

@return works fine for evaluating whether the procedure had an error or not though I also want to evaluate the error itself. Am I missing something? Is there a better way?

Derrick Walker

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-15 : 10:33:22

@@ERROR has already been reset to 0 by the time your SELECT @error... executes.
Why not capture @@ERROR inside the SP and put the value in @Return. Then, if @Return is 0, no error, otherwise @Return contains the error code.

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 10:55:35

quote:
Originally posted by WalkerDA

So what is done about errors that cannot be trapped? For instance if I want to execute a line of code if a procedure throws an error is it best to simply get the return value 0 or 1

SET XACT_ABORT ON might be of some help

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-15 : 11:17:57

Give this thread a read...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35642

Brett

8-)

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-15 : 14:22:17

quote:
Originally posted by X002548

Give this thread a read...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35642

Brett

8-)

The thread was pretty intense, I enjoyed! So correct me if I'm wrong, but you are saying that by using a return value then that would handle system-level errors.

Kristen, the XACT_ABORT was an awesome note, thanks...

Derrick Walker

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-15 : 16:14:41

What exactly does XACT_ABORT do? I know for like a distributed query it needs to b set but I don't know why

Keithc MCSE MCSA

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-15 : 16:42:00

From what I've read it has the ability to ROLLBACK all statements within the current transaction if a Transact-SQL statement raises a run-time error.

Check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_0rqs.asp

From my limited experience in cases such as this there is no way to error trap within a procedure in these cases. So this little nugget will prove a great tool.

Derrick Walker

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-15 : 17:47:30

That brings up another question, I'm very new to sql still so forgive me if this sounds dumb but why not just use xact_abort instead of like an raiserror or @@error check into a @return variable? Also couldn't you do the same thing as xact_abort with like @@error using a rollback
EX
Declare @var int
Set @var = @@error
If (@Var != 0)
Rollback Tran

Keithc MCSE MCSA

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 17:52:47

Beware that XACT_ABORT will not report an error to a calling SProc. It seems to me that using XACT_ABORT means that doing "IF @@ERROR <> 0 RAISERROR(..." is all-but pointless because XACT_ABORT has performned an abort before IF @@ERROR ... can catch the exception, but that's not the case with nested SProcs, so you do still need:

EXEC MySProc ...
SELECT @MyErrNo = @@ERROR
IF @MyErrNo <> 0 ... abort ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources