Re: Stored Procedure error is not catched

Hello,
You might consider exception handling in your procedure.

I have following utility procedure:
-- Checks if @error_code is bigger than 0 and eventually fetches system error messages and procedure name based on @procid
-- and raises exception.
-- @error_code - use @@ERROR
-- @procid - use @@PROCID
-- example:
-- create procedure safe_delete as
-- begin
-- delete from table_a where id = 233
-- exec dbUtil_pkg$check_error(@@ERROR, @@PROCID)
-- end
create procedure dbUtil_pkg$check_error(@error_code int, @msg varchar(300)) as
declare
@complete_msg varchar(400)
begin
if @error_code > 0
begin
set @complete_msg = @msg + ': failed with error code ' + cast( @error_code as varchar)
raiserror ( @complete_msg , 16, 1)
end
end
go


And then in the code I use it:
            -- mark rows for syncing
            UPDATE searchable_object_wa SET sync_flag = 1;

-- remove all rows from searchable_attribute for objects that need update
delete from searchable_attribute
where exists (select 1
from searchable_object_wa wa WITH (NOLOCK)
where searchable_attribute.lookup_id = wa.lookup_id
and wa.sync_flag = 1);
exec dbUtil_pkg$check_error @@ERROR, @v_signature;

            -- update master rows in searchable_object table
            -- handle so deletes
            delete from searchable_object
            where exists (select wa.id
                      from searchable_object_wa wa WITH (NOLOCK)
                      where wa.lookup_id = searchable_object.lookup_id
                      and wa.sync_flag = 1
                      and wa.deleted = 1);
            exec dbUtil_pkg$check_error @@ERROR, @v_signature;

            -- delete all deleted entries in wa
            delete from searchable_object_wa
            where deleted = 1
                AND sync_flag = 1;
            exec dbUtil_pkg$check_error @@ERROR, @v_signature;

It sort of mimics behaviour of stored procedures from other more advanced databases. :-)
Hope that helps,
Radovan


Rizwan wrote:
I am using MS SQL Server JDBC Driver. I call a stored procedure from my java code. The Stored Procedure does some inserts. One of the insert failed but in my java code the SQLException is not thrown. Can anybody tell me how to fix this bug?



Thanks


.

Loading