community.borland.com

Article #25835: Joining two stored procedures

Problem:
Can I execute a join on two stored procedures?

Solution:
The Information in this article applies to:
* InterBase v5.x
* InterBase v6.x

Using stored procedures in a join statement is allowed.  

Summarizing the Language Guide for the SELECT statement:

SELECT ... FROM  [ ,  ... ] ...

 =  | table | view | procedure ...

shows that you can use a stored procedure in a join statement.

Here is an example demonstrating this.  This example uses 
employee.gdb.

Here are the two stored procedure definitions that will be used
in the join statement.

CREATE PROCEDURE GETEMPS 
RETURNS (EMP_NO INTEGER, FULL_NAME VARCHAR(120))
AS begin
  for select emp_no, full_name from employee into :emp_no, :full_name
  do
    suspend;
end!!
 
CREATE PROCEDURE GETPHONENO 
RETURNS (EMP_NO INTEGER, PHONE_NO VARCHAR(20))
AS begin
  for select emp_no, phone_no from phone_list into :emp_No, :phone_no
  do
    suspend;
end!!

Now execute a query that joins the two statements

select * from getemps e, getphoneno p where e.emp_no = p.emp_no;

or if you prefer SQL92 syntax:

select * from getemps e join getphoneno p on e.emp_no=p.emp_no;

Last Modified: 20-OCT-00