Error Handling in Stored Procedure

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 Handling in Stored Procedure

Author

Topic

cthompson
Starting Member

11 Posts

Posted - 2004-04-14 : 05:11:48

When a system error occurs (i.e. failed to update) I need to insert the person_id into another table to know which person had a system error (any system error) and also rollback the transaction so that no data was committed to the table.

This procedure would run several times and i would not like the procedure to stop if there is an error. but would like to continue to process other files.

The problem is that even though there is an system error(i.e I renamed the table 'test_stmaos' to 'copy_test_stmaos' to generate an error) and the data was rolled back, the person was not inserted into the syserror table as I wished.

-------------------------------------------------------------------
This is a small example of the stored procedure that I am trying to run.

declare @person_id char(11)
declare @program_code char(11)
declare @error int

select @person_id = 'MOT01012939'
select @program_code = '26PJ0001'

if @person_id not in(select person from test_online_invalid_person) and
@person_id not in(select person_id from test_online_failed)

begin transaction
Update test_stmaos set stage_code = 'R01'
where person_id = @person_id and code = @program_code
if (@@error <> 0)
set @error = @@error
if @error <> 0
begin
rollback transaction
insert into test_online_syserror (person_id)
values (@person_id)
end

commit transaction

kroky
Starting Member

14 Posts

Posted - 2004-04-14 : 07:07:15

There are some things that are wrong in your query...
1 when you get an error you shold stop the execution or place the commit transactin in else .. now you may try to rollback and commit afterwards which will generate an error
2
if (@@error <> 0)
set @error = @@error
if @error <> 0
begin
this also would not work since @@error keeps the error from the last execution so in this case it will be @@Error <> 0 which will pass successfuly so @error will always be 0 so you should use
set @error = @@error
if @error <> 0
begin

and finally there are errors that stop the execution of procedures and queries when they appear (Books online read about severity levels) thera are also errors that can close your connection to the DB .. so you will never be sure that you will be able to add this person to some other table...

cthompson
Starting Member

11 Posts

Posted - 2004-04-14 : 09:00:52

I have changed the transact-SQL to include an error handler

example:
-----------------------------------------------------
begin transaction
Update test_stmaos set stage_code = 'R01'
where person_id = @person_id and code = @program_code
set @error =@@error
if @error<>0 goto err_handler
commit transaction

err_handler:
rollback transaction
insert into test_online_syserror (person_id)
values (@person_id)
------------------------------------------------------
I have then tried to simulate a system error by changing a name of our test table and the rollback occurs, but the person_id is not inserted into test_online_syserror table.

I need to be able to insert the person_id into the syserror table, when a system error occurs.

Is this feasable.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 09:56:20

Did you change it to a non existant table?

That's a type of error you cann't trap

You got a pk or a unique index?

Try inserting a dup key...that'll get trapped...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources