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

firebird, select, sql, stored-procedures

Solution

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 ; ^

Problem

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"

Original source