CODEWITHSUNDEEP
sqlstored-proceduresselectfirebird
zeqk
zeqk

Reputation: 85

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

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"

Upvotes: 7

Views: 9739

Answers (1)

Douglas Tosi
Douglas Tosi

Reputation: 2350

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

Upvotes: 11

Related Questions