Using Stored Procedures that Return Result Sets
From InterBase
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 aSELECT
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 toTrue
or invoke theOpen
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)