Working with Stored Procedures
Go Up to Developer's Guide
This chapter describes how to use stored procedures in your database applications. A stored procedure is a self-contained program written in the procedure and trigger language specific to the database system used. There are two fundamental types of stored procedures. The first type retrieves data (as with a SELECT query). The retrieved data can be in the form of a dataset consisting of one or more rows of data, divided into one or more columns. Alternatively, the retrieved data can be in the form of individual pieces of information. The second type does not return data but performs an action on data stored in the database (as with a DELETE statement).
InterBase servers return all data (datasets and individual pieces of information) exclusively with output parameters.
In InterBase Express applications, access to stored procedures is provided by the TIBStoredProc and TIBQuery components. The choice of which to use for the access is predicated on how the stored procedure is coded, how data is returned (if any), and the database system used. The TIBStoredProc and TIBQuery components are both descendants of TIBCustomDataSet and inherit behaviors from TIBCustomDataSet. For more information about TIBCusomDataSet, see Understanding Datasets.
A stored procedure component is used to execute stored procedures that do not return any data, to retrieve individual pieces of information in the form of output parameters, and to relay a returned dataset to an associated data source component. The stored procedure component allows values to be passed to and return from the stored procedure through parameters, each parameter defined in the Params property. The stored procedure component is the preferred means for using stored procedures that either do not return any data or only return data through output parameters.
A query component is primarily used to run InterBase stored procedures that only return datasets via output parameters. The query component can also be used to execute a stored procedure that does not return a dataset or output parameter values.
Use parameters to pass distinct values to or return values from a stored procedure. Input parameter values are used in such places as the WHERE clause of a SELECT statement in a stored procedure. An output parameter allows a stored procedure to pass a single value to the calling application. Some stored procedures return a result parameter. See “Input parameters” and “Output parameters” in the “Procedures and Triggers” chapter of the Language Reference Guide, and “Working with Stored Procedures” in the Data Definition Guide for more information.