Problems with @@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

Transact-SQL (2000)

Problems with @@error

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-22 : 10:50:51

I have a procedure with begin tran - commit, and I have insert into table wich doesn't exist. @@error doesn't return anything and I have begin tran open

create procedure exampleProc
as
begin
set nocount on
begin tran
update OJ set tip = isnull(tip,0) + 1
insert into mm1 values (1,2)
if @@error<>0
begin
rollback tran
return
end
commit tran
set nocount off
end

I start exampleProc in one screen and 'select * from OJ' on the other screen of Query Analyzer. Table OJ is locked. Procedure didn't pass through 'if @@error <> 0'.
Why, and how can I handle this

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 11:16:48

You need to check @@ERROR after each statement. In your example, you are checking to see if the INSERT errored, not the UPDATE.


create procedure exampleProc
as
begin
set nocount on
begin tran
update OJ set tip = isnull(tip,0) + 1
IF @@ERROR <> 0 GOTO ErrorTrap
insert into mm1 values (1,2) 
IF @@ERROR <> 0 GOTO ErrorTrap
COMMIT TRAN
GOTO Finish
ErrorTrap:
    ROLLBACK TRAN
Finish:
    SET NOCOUNT OFF
END

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-23 : 02:43:09

I don't have a problem with update statement. I have a problem that table MM1 does not exists, there is no insert in MM1, so error appears. But 'IF @@error <> 0' does not handle this situation. Transaction is open.
You can do 'select * from OJ' from the same connection, but not from different one.

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 04:51:13

Does
SET XACT_ABORT ON
help? (I'm not sure what it does to your open transaction

Also, if you wrap this SProc in another you should get an @@ERROR reported to that (but I haven't test it!)

EXEC exampleProc
IF @@ERROR <> 0 GOTO ErrorTrap2

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources