Using Stored Procedures that Perform Actions on Data

Go Up to Using a Stored Procedure

Stored procedures can be coded such that they do not return any data at all, and only perform some action in the database. SQL operations involving the INSERT and DELETE statements are good examples of this type of stored procedure. For instance, instead of allowing a user to delete a row directly, a stored procedure might be used to do so. This would allow the stored procedure to control what is deleted and also to handle any referential integrity aspects, such as a cascading delete of rows in dependent tables.

Executing an Action Stored Procedure with a TIBQuery

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”)

Executing an action stored procedure 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.

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 Delphi code to execute the ADD_EMP_PROJ stored procedure is:

with StoredProc1 do begin
  StoredProcName := ‘ADD_EMP_PROJ’;
  ExecProc;
end;

Advance To:

• Understanding Stored Procedure Parameters