How to perform a procedure that returns a result set in Firebird

Question

I have the following table

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

with three 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 a 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

only one top row is returned

COL1              COL2
Victorias Secret  true

Please report what is wrong. How should I execute it to see all 3 rows that it is intended to return?

Answer 1

When you have it suspendin a stored procedure, it is called "selectable saved sprocedure", and as the name implies, you select it, therefore:

select * from LIST_PIPE

Answer 2

ain , SELECT * FROM <your procedure> ( : SUSPEND).

Interbase 6 Embedded SQL Guide (. InterBase 6.0) :

, :

• , SELECT. select .
• , , EXECUTE PROCEDURE. .

CREATE PROCEDURE . , . , . - , , .

, , -, , . SELECT ( ) EXECUTE PROCEDURE ( ).

EXECUTE PROCEDURE ( ), SELECT * FROM <procedure> , . , EXECUTE PROCEDURE, Firebird .

, EXECUTE PROCEDURE . Interbase 6 SUSPEND "SUSPEND ". ( , SUSPEND - , , EXECUTE PROCEDURE ).