Using Select Procedures

From InterBase

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;
Important:
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);
}

Advance To: