How to execute procedure returning resultset in Fi

Question

I have the following table

create table LIST_PIPE_TABLE
(
  ID INT,
  ITEM          VARCHAR(4000),
  IS_FOLDER     VARCHAR(10)
)

with 3 rows of data

insert into LIST_PIPE_TABLE values(1,'Victorias Secret','true')
insert into LIST_PIPE_TABLE values(2,'Porsche','true')
insert into LIST_PIPE_TABLE values(3,'Babbolat','false')

And a stored procedure that should return resultset

CREATE or alter PROCEDURE LIST_PIPE
RETURNS 
(   col1 varchar(4000),
    col2 varchar(10)
)
  AS  
begin
    FOR SELECT ITEM AS ITEM
       ,IS_FOLDER AS IS_FOLDER
      FROM LIST_PIPE_TABLE 
    into :col1, :col2
    do begin       
            suspend;          
     end 
end

When I try to execute it with the following statement

execute procedure LIST_PIPE

the only one top row is returned

COL1              COL2
Victorias Secret  true

Please advise what is wrong about it. How should I execute it to see all 3 rows it is designed to return?

Answer 1

When you have suspend in stored procedure, it is called "selectable stored sprocedure", and as the name says you select from it, so:

select * from LIST_PIPE

Answer 2

As ain already answered, you need to use SELECT * FROM <your procedure> for a selectable procedure (that is: it contains a SUSPEND).

The Interbase 6 Embedded SQL Guide (see under InterBase 6.0 Manuals) says:

There are two types of procedures that can be called from an application:

• Select procedures that an application can use in place of a table or view in a SELECT statement. A select procedure must return one or more values, or an error results.
• Executable procedures that an application can call directly, with the EXECUTE PROCEDURE statement. An executable procedure may or may not return values to the calling program.

Both kinds of procedures are defined with CREATE PROCEDURE and have the same syntax. The difference is in how the procedure is written and how it is intended to be used. Select procedures always return zero or more rows, so that to the calling program they appear as a table or view. Executable procedures are simply routines invoked by the calling program that can return only a single set of values.

In fact, a single procedure conceivably can be used as a select procedure or an executable procedure, but this is not recommended. In general a procedure is written specifically to be used in a SELECT statement (a select procedure) or to be used in an EXECUTE PROCEDURE statement (an executable procedure).

On a protocol level, an EXECUTE PROCEDURE statement will always produce a single row of results (which might be empty), where as a SELECT * FROM <procedure> will behave the same as a select from a table or view. This means that if a selectable procedure is called with EXECUTE PROCEDURE, that Firebird itself will fetch only one row from the stored procedure, and then end execution of the procedure.

It is unfortunate that it is possible to use EXECUTE PROCEDURE with a selectable procedure. The Interbase 6 Language Reference on SUSPEND explicitly mentions "SUSPEND should not be used in an executable procedure." (the phrasing is weird, because the presence of SUSPEND is what makes it selectable, though here they mean calling it with EXECUTE PROCEDURE is not advisable).