7

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"

Tom H
  • 46,766
  • 14
  • 87
  • 128
zeqk
  • 85
  • 1
  • 5
  • Advice: don't use quotes in identifiers unless you really understand what it does and why you do need it. – Fr0sT Oct 30 '14 at 13:30

1 Answers1

11

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 ; ^
Cwt
  • 8,206
  • 3
  • 32
  • 27
Douglas Tosi
  • 2,310
  • 2
  • 18
  • 18
  • 1
    If I have 'n' (10) the number of columns, then I should mention all those columns individually? Is there any alternate solution for this case? – Kathir Subramaniam Mar 08 '19 at 12:49