Selectable Procedures

Selectable stored procedures usually retrieve data from a database, returning an arbitrary number of rows to the caller.The caller receives the output one row at a time from a row buffer that the database engine prepares for it.

Selectable procedures can be useful for obtaining complex sets of data that are often impossible or too difficult or too slow to retrieve using regular DSQL SELECT queries.Typically, this style of procedure iterates through a looping process of extracting data, perhaps transforming it before filling the output variables (parameters) with fresh data at each iteration of the loop.A [fblangref50-psql-suspend] statement at the end of the iteration fills the buffer and waits for the caller to fetch the row.Execution of the next iteration of the loop begins when the buffer has been cleared.

Selectable procedures may have input parameters, and the output set is specified by the RETURNS clause in the header.

A selectable stored procedure is called with a SELECT statement.See an example of a selectable stored procedure at the end of the CREATE PROCEDURE section of [fblangref50-ddl].

The syntax for creating executable stored procedures and selectable stored procedures is the same.The difference comes in the logic of the program code, specifically the absence or presence of a [fblangref50-psql-suspend] statement.

For information about creating stored procedures, see CREATE PROCEDURE in [fblangref50-ddl].