I'm writing a stored procedure to update the values of multiple tables, and
looking for confirmation that the following construct will perform as I
understand it:

create procedure change_location (ACCOUNT_ID integer, OLD_LOCATION_NBR
integer, NEW_LOCATION_NBR integer)
returns (TABLE_NAME varchar(31),
COLUMN_NAME varchar(31), CHANGE_COUNT integer, ERROR_MESSAGE varchar(200))
as
begin
begin
Table_name = 'Location';
Column_name = 'Location_ID';
Error_message = NULL;
update
Location
set
location_id = :vNewLocationID
where
location_id = :vOldLocationID;
Change_count = row_count;
when any do
Error_message = sqlcode;
end
suspend;

begin
Table_name = 'Employee';
Column_name = 'Location_ID';
Error_message = NULL;
update
employee
set
location_id = :vNewLocationID
where
location_id = :vOldLocationID;
Change_count = row_count;
when any do
Error_message = sqlcode;
end
suspend;
end


My understanding is that this stored procedure will always return two
rows. If either of the two updates fail, a row will be returned with the
sqlcode as the error message for the appropriate failure, but the other one
will succeed. Is that correct?

Is there any way to get the textual error message based on the sqlcode?

tia,

Bob M..