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);
Calling a Select Procedure
To use a select procedure in place of a table or view name in an application, use the procedure name anywhere a table or view name is appropriate. Supply any input parameters required in a comma-delimited list in parentheses following the procedure name.
EXEC SQL SELECT PROJ_ID FROM GET_EMP_PROJ (:emp_no) ORDER BY PROJ_ID;
InterBase does not support creating a view by calling a select procedure.
Using a Select Procedure with Cursors
A select procedure can also be used in a cursor declaration. For example, the following code declares a cursor named PROJECTS
, using the GET_EMP_PROJ
procedure in place of a table:
EXEC SQL DECLARE PROJECTS CURSOR FOR SELECT PROJ_ID FROM GET_EMP_PROJ (:emp_no) ORDER BY PROJ_ID;
The following application C code with embedded SQL then uses the PROJECTS
cursor to print project numbers to standard output:
EXEC SQL OPEN PROJECTS /* Print employee projects. */ while (SQLCODE == 0) { EXEC SQL FETCH PROJECTS INTO :proj_id :nullind; if (SQLCODE == 100) break; if (nullind == 0) printf("\t%s\n", proj_id); }