Using Stored Procedures that Return Result Sets

Go Up to Using a Stored Procedure

Stored procedures that return data in datasets, rows and columns of data, should most often be used with a query component. However, a stored procedure component can also serve this purpose.

Retrieving a Result Set with a TIBQuery

To retrieve a dataset from a stored procedure using a TIBQuery component:

1. Instantiate a query component.

2. In the TIBQuery.SQL property, write a SELECT query that uses the name of the stored procedure instead of a table name.

3. If the stored procedure requires input parameters, express the parameter values as a comma-separated list, enclosed in parentheses, following the procedure name.

4. Set the Active property to True or invoke the Open method.

For example, the InterBase stored procedure GET_EMP_PROJ, below, accepts a value using the input parameter EMP_NO and returns a dataset through the output parameter PROJ_ID.

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

The SQL statement issued from a TIBQuery to use this stored procedure would be:

SELECT *
FROM GET_EMP_PROJ(52)

Advance To:

• Using Stored Procedures that Return Data Using Parameters