How to abort stored procedure on error (@@ERROR

1 Sep 2005 8:24 PM

Radovan Biciste

Hello,
I'm still learning SQL Server. Right now I'm trying to find generic way
to handle exception. I would like to have a stored procedure that would
check @@ERROR and abort execution of parent procedure and raise the
exception to client. We are using JDBC to connect to database.
It should like this:

create procedure a_test as
....
insert into a_table values (1,3,4)
exec dbUtil_pkg$check_error(@@ERROR)
update a_table set name='aaa' where id=1
....
end

Right now the problem is that even if there is an exception the stored
procedure continues in execution.
The only way I found so far is to do something like this:

create procedure a_test as
....
insert into a_table values (1,3,4)
if @@ERROR > 0 return @@ERROR
update a_table set name='aaa' where id=1
....
end

It does not raise the exception to a client though. I would have to loop
through all results of queries to retrieve it.
It might help to note that I'm porting our application from Oracle to
SQL Server and we are trying to mimic Oracle's behaviour.
Hope that makes sense,
Radovan

1 Sep 2005 8:34 PM

Alejandro Mesa

Use RAISERROR and RETURN.

declare @error int

insert ...

set @error = @@error

if @error > 0
   begin
   raiserror('blablabla...', 16, 1)
   return @error
   end
....

You will like this.

Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html

Error Handling in SQL Server â€“ a Background
http://www.sommarskog.se/error-handling-I.html

AMB

Show quote

"Radovan Biciste" wrote:

> Hello,
> I'm still learning SQL Server. Right now I'm trying to find generic way
> to handle exception. I would like to have a stored procedure that would
> check @@ERROR and abort execution of parent procedure and raise the
> exception to client. We are using JDBC to connect to database.
> It should like this:
>
> create procedure a_test as
> ....
> insert into a_table values (1,3,4)
> exec dbUtil_pkg$check_error(@@ERROR)
> update a_table set name='aaa' where id=1
> ....
> end
>
> Right now the problem is that even if there is an exception the stored
> procedure continues in execution.
> The only way I found so far is to do something like this:
>
> create procedure a_test as
> ....
> insert into a_table values (1,3,4)
> if @@ERROR > 0 return @@ERROR
> update a_table set name='aaa' where id=1
> ....
> end
>
> It does not raise the exception to a client though. I would have to loop
> through all results of queries to retrieve it.
> It might help to note that I'm porting our application from Oracle to
> SQL Server and we are trying to mimic Oracle's behaviour.
> Hope that makes sense,
> Radovan
>

How to abort stored procedure on error (@@ERROR > 0)