Dear Storage Box,

The difference between select stored procedures and execute
stored procedures is easy to miss. If your procedure will return
more than one row of data, it's a select stored procedure and

1) may use a loop, ordinarily a FOR SELECT loop, to generate
values

2) has a SUSPEND statement marking the point at which data
is to be returned to the user. If the procedure uses a
loop to generate data, the SUSPEND is inside the loop.

3) is invoked with a SELECT statement.

If your procedure returns only one row of data, it's an execute
stored procedure and

1) may or may not use a loop to generate data.

2) may or may not include a SUSPEND statement.

3) is invoked with an EXECUTE PROCEDURE statement.

There is no check made to keep you from invoking a select procedure
with an EXECUTE PROCEDURE statement or invoking an execute procedure
with a SELECT statement. You just get the wrong answer. That may
not have been the very best possible design.

Here are examples of a very simple select procedure and a very
simple execute procedure and the results produced by each from
invoking them in each method:

Select procedure:

create procedure foobar returns (x integer)
as begin
x = 0;
while (x < 10) do
begin
x = x + 1;
suspend;
end
end^

SQL> select * from foobar;

X
============

1
2
3
4
5
6
7
8
9
10


SQL> execute procedure foobar;

X
============
1



Execute procedure:

create procedure xxxx returns (x integer)
as begin
x = 1;
end^

SQL> select * from xxxx;
SQL> execute procedure xxxx;

X
============
1


Note that, without a SUSPEND, the SELECT statement returns nothing.
Adding a SUSPEND to the execute procedure turns it into a (really
feeble) select procedure whch returns the expected result whichever
way it's invoked.

create procedure yyyy returns (x integer)
as begin
x = 1;
suspend;
end^

SQL> select * from yyyy;

X
============
1

SQL> execute procedure yyyy;

X
============
1

Hope this makes a murky situation just a bit clearer...

Regards,

Ann