Hello Marten,

> I am trying to write a stored procedure which will perform a select
> that returns one record. My problem is how to correctly use SQLCODE.
> (I have read about error-handling in "Language Reference" and
> "Embedded SQL Guide").

After the code that might raise an error, put:

when sqlcode -xxx do
begin
<error-handling-code>
end

where "-xxx" is the 3-digit negative sql errorcode.

If you use gdscode instead of sqlcode, there are 2 benefits:

1: gdscode is more precise: there are ususally several gdscodes
(ISC eror codes) per sqlcode;
2: Instead of numbers, you can use constant names like db_corrupt
etc.

There's also a disadvantage: gdscode isn't portable to other RDMSes.


But in your situation you want to test whether or not a select
returned anything. An empty result set is not an error however, so no
error will be raised. "somevariable" will be null, that's all.

I would do something like this:

if exists ( select... from... where... ) then
begin
<select it into somevariable>
end
else
begin
<do something else>
end

Alternatively, if the first select (if it succeeds) can't return null,
you could keep your existing code and test "somevariable is null"
after the first select.

> begin
> select field
> from table
> where condition
> into somevariable
>
> if no_records_found then
> begin
> select field
> from table
> where some_other_condition
> into somevariable
> end
> end

HTH,
Paul Vinkenoog