How to do a firebird SP, SELECT * FROM "TABLE"?

Question

I'm migrating from SQL Server to Firebird.

In SQL Server

CREATE PROCEDURE Departments_GetAll
AS
    SELECT * FROM Departments

I try in Firebird

CREATE PROCEDURE DEPARTMENTS_DELETEALL
AS 
BEGIN
  SELECT * FROM "Departments";
END^

SET TERM ; ^

But that doesn't work.

It returns an error "SQL Code -104"

Answer 1

A stored procedure needs return parameters and the suspend command in order to return multiple rows.

CREATE PROCEDURE DEPARTMENTS_GET_ALL
returns(id integer, name varchar(100))
AS 
BEGIN
  FOR SELECT id, name
  FROM "Departments"
  into :id, :name do
  BEGIN
    SUSPEND;
  END
END^

SET TERM ; ^