Using Select Procedures in isql

Go Up to Using Stored Procedures

A select procedure is used in place of a table or view in a SELECT statement and can return a single row or multiple rows.

The advantages of select procedures over tables or views are:

• They can take input parameters that can affect the output.
• They can contain logic not available in normal queries or views.
• They can return rows from multiple tables using UNION.

The syntax of SELECT from a procedure is:

SELECT <col_list> from name ([param [, param …]])
WHERE <search_condition>
ORDER BY <order_list>;

The procedure <name> must be specified, and in isql each <param> is a constant passed to the corresponding input parameter. All input parameters required by the procedure must be supplied. The <col_list> is a comma-delimited list of output parameters returned by the procedure, or * to select all rows.

The WHERE clause specifies a <search_condition> that selects a subset of rows to return. The ORDER BY clause specifies how to order the rows returned. For more information on SELECT, see the Language Reference.

Note: The following code defines the procedure, GET_EMP_PROJ,which returns EMP_PROJ, the project numbers assigned to an employee, when it is passed the employee number, EMP_NO, as the input parameter.

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (EMP_PROJ SMALLINT) AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :EMP_PROJ
DO
SUSPEND;
END ;

The following statement selects from GET_EMP_PROJ in isql:

SELECT * FROM GET_EMP_PROJ(24);

The output is:

PROJ_ID
=======
DGPII
GUIDE

The following select procedure, ORG_CHART, displays an organizational chart:

CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
AS
DECLARE VARIABLE MNGR_NO INTEGER;
DECLARE VARIABLE DNO CHAR(3);
BEGIN
FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
FROM DEPARTMENT D
LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
ORDER BY D.DEPT_NO
INTO :HEAD_DEPT, :DEPARTMENT, :MNGR_NO, :DNO
DO
BEGIN
IF (:MNGR_NO IS NULL) THEN
BEGIN
MNGR_NAME = '--TBH--';
TITLE = '';
END
ELSE
SELECT FULL_NAME, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NO = :MNGR_NO
INTO :MNGR_NAME, :TITLE;
SELECT COUNT(EMP_NO)
FROM EMPLOYEE
WHERE DEPT_NO = :DNO
INTO :EMP_CNT;
SUSPEND;
END
END ;

ORG_CHART is invoked from isql as follows:

SELECT * FROM ORG_CHART;

For each department, the procedure displays the department name, the department’s “head department” (managing department), the department manager’s name and title, and the number of employees in the department.

HEAD_DEPT ================	DEPARTMENT ===============	MNGR_NAME =============	TITLE =====	EMP_CNT ======
	Corporate Headquarters	Bender, Oliver H.	CEO	2
Corporate Headquarters	Sales and Marketing	MacDonald, Mary S.	VP	2
Sales and Marketing	Pacific Rim Headquarters	Baldwin, Janet	Sales	2
Pacific Rim Headquarters	Field Office: Japan	Yamamoto, Takashi	SRep	2
Pacific Rim Headquarters	Field Office: Singapore	—TBH—		0

ORG_CHART must be used as a select procedure to display the full organization. If called with EXECUTE PROCEDURE, then the first time it encounters the SUSPEND statement, the procedure terminates, returning the information for Corporate Headquarters only.

SELECT can specify columns to retrieve from a procedure. For example, if ORG_CHART is invoked as follows:

SELECT DEPARTMENT FROM ORG_CHART;

then only the second column, DEPARTMENT, is displayed.

Topics

• Using WHERE and ORDER BY Clauses
• Selecting Aggregates from Procedures