Executing an Action Stored Procedure with a TIBQuery

Go Up to Using Stored Procedures that Perform Actions on Data

To execute an action stored procedure using a TIBQuery component:

1. Instantiate a query component.

2. In the TIBQuery.SQL property, include the command necessary to execute the stored procedure and the stored procedure name. (The command to execute a stored procedure can vary from one database system to another. In InterBase, the command is EXECUTE PROCEDURE.)

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. Invoke the TIBQuery.ExecSQL method.

For example, the InterBase stored procedure ADD_EMP_PROJ, below, adds a new row to the table EMPLOYEE_PROJECT. No dataset is returned and no individual values are returned in output parameters.

CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
AS
BEGIN
  BEGIN
    INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID)
    VALUES (:EMP_NO, :PROJ_ID);
    WHEN SQLCODE -530 DO
      EXCEPTION UNKNOWN_EMP_ID;
  END
  SUSPEND;
END

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

EXECUTE PROCEDURE ADD_EMP_PROJ(20, “GUIDE”)