FB Selectable Stored Procedure

The stored procedure (SP) in Firebird can be used as "view/table", it means we can make not only executable SP but selectable SP. The selectable SP can return multiple record as you can do the same with view/table. The things that can make it happen is the keyword "SUSPEND". The simple example is:

CREATE PROCEDURE GET_EMP_PROJ(
 EMP_NO SMALLINT)
RETURNS (
 PROJ_ID CHAR(5))
AS
BEGIN
 FOR SELECT proj_id
     FROM employee_project
     WHERE emp_no = :emp_no
 INTO :proj_id
 DO
   SUSPEND;
END

The above example can be found on the employee example database when you install firebird. The "INTO" keyword must be followed by the return variable (declare in the header of the SP) then we must put the keyword "SUSPEND" after that. The advantage of this selectable SP is we can make it more complex that include some business rule before we return the value that can be selectable, and we can also has a parameters to output different result based on that parameters. As I know there is no much database that has this ability (I only know Oracle, Firebird/Interbase that can do that, perhaps PostgreeSQL).

We can make this selectable as the usual table, it means we can join it with the other table, view or selectable SP. Before Firebird version 2, we often used it as the solution to make derived table as we know before version 2 the derived table is not supported in FB.