@ERROR not working - 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)

@ERROR not working

Author

Topic

jag_chat
Starting Member

3 Posts

Posted - 2004-01-28 : 21:35:12

I wrote a store procedure like the following

create procedure....as
begin
declare @e int
select @e = 0
begin trans
insert.....
if @@ERROR<>0
begin
@e = @@ERROR
print "...."
end
if @@ERROR<>0
begin
@e = @@ERROR
print "...."
end
if @e=0 then
commit transaction
else
rollback transaction
return 0

it is not working....when any error (especially wrong table name)...it is raising an error and no commit or rollback getting executed in the second statement....no other setting is made apart from the above code.

jek
Starting Member

9 Posts

Posted - 2004-01-29 : 00:55:43

Hi

For this to work, you should save the @@ERROR in a variable. (Reason is @@ERROR contains error/result of most recently executed code.)

declare @ERRORID int

insert.....

--- ** save the error before using it anywhere **
set @ERRORID=@@ERROR

if @ERRORID<>0
begin
print "error encountered"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources