Using Stored Procedures that Return Data Using Parameters

Go Up to Using a Stored Procedure

Stored procedures can be composed to retrieve individual pieces of information, as opposed to whole rows of data, through parameters. For instance, a stored procedure might retrieve the maximum value for a column, add one to that value, and then return that value to the application. Such stored procedures can be used, and the values inspected using either a TIBQuery or a TIBStoredProc component. The preferred method for retrieving parameter values is with a TIBStoredProc.

Retrieving Individual Values with a TIBQuery

Parameter values retrieved via a TIBQuery component take the form of a single-row dataset, even if only one parameter is returned by the stored procedure. To retrieve individual values from stored procedure parameters 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. The SELECT clause of this query can specify the parameter by its name as if it were a column in a table, or it can simply use the * operator to retrieve all parameter values.
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_HIGH_EMP_NAME, below, retrieves the alphabetically last value in the LAST_NAME column of a table named EMPLOYEE. The stored procedure returns this value in the output parameter High_Last_Name.

CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS
BEGIN
  SELECT MAX(LAST_NAME)
  FROM EMPLOYEE
  INTO :High_Last_Name;
  SUSPEND;
END

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

SELECT High_Last_Name
FROM GET_HIGH_EMP_NAME

Retrieving Individual Values with a TIBStoredProc

To retrieve individual values from stored procedure output parameters using a TIBStoredProc component:

1. Instantiate a stored procedure component.
2. In the StoredProcName property, specify the name of the stored procedure.
3. If the stored procedure requires input parameters, supply values for the parameters using the Params property or ParamByName method.
4. Invoke the ExecProc method.
5. Inspect the values of individual output parameters using the Params property or ParamByName method.

For example, the InterBase stored procedure GET_HIGH_EMP_NAME, below, retrieves the alphabetically last value in the LAST_NAME column of a table named EMPLOYEE. The stored procedure returns this value in the output parameter High_Last_Name.

CREATE PROCEDURE GET_HIGH_EMP_NAME
RETURNS (High_Last_Name CHAR(15))
AS
BEGIN
  SELECT MAX(LAST_NAME)
  FROM EMPLOYEE
  INTO :High_Last_Name;
  SUSPEND;
END

The Delphi code to get the value in the High_Last_Name output parameter and store it to the Text property of a TEdit component is:

with StoredProc1 do begin
  StoredProcName := 'GET_HIGH_EMP_NAME';
  ExecProc;
  Edit1.Text := ParamByName('High_Last_Name').AsString;
end;

Advance To:

• Using Stored Procedures that Perform Actions on Data