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