Using Select Procedures
Go Up to Working with Stored Procedures
A select procedure is used in place of a table or view in a SELECT statement and can return zero or more rows. A select procedure must return one or more output parameters, or an error results. If returned values are not specified, NULL values are returned by default.
The advantages of select procedures over tables or views are:
- They can take input parameters that can affect the output produced.
- They can contain control statements, local variables, and data manipulation statements, offering great flexibility to the user.
Input parameters are passed to a select procedure in a comma-delimited list in parentheses following the procedure name.
The following isql script defines the procedure, GET_EMP_PROJ, which returns EMP_PROJ, the project numbers assigned to an employee, when 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 retrieves PROJ_ID from the above procedure, passing the host variable, number, as input:
SELECT PROJ_ID FROM GET_EMP_PROJ (:number);