CLOSE STORED-PROCEDURE statement

For a non-ABL stored procedure, indicates that the procedure has completed execution and retrieves any return status. For a send-sql-statement stored procedure, closes the SQL cursor used by the procedure.


CLOSE STORED-PROCEDURE `procedure` [ `integer-field` = PROC-STATUS ] [ WHERE PROC-HANDLE = `integer-field` ]

The name of the stored procedure that you want to close or the built-in procedure name, send-sql-statement.

Assigns the return value from a stored procedure to the specified integer field or variable (integer-field).

An integer field or variable whose value uniquely identifies the stored procedure that produces the results returned from the data source or the SQL cursor of a send-sql-statement stored procedure.

The PROC-STATUS clause of the CLOSE STORED-PROCEDURE statement allows the DataServer for ORACLE to retrieve the text of an ORACLE error message that was passed to raise_application_error. Use the ERROR-STATUS:GET-MESSAGE handle to retrieve the message, as shown in the following example:

DEFINE VARIABLE st AS INTEGER NO-UNDO. DEFINE VARIABLE h AS INTEGER NO-UNDO. RUN STORED-PROC p1 h = PROC-HANDLE NO-ERROR. CLOSE STORED-PROC p1 st = PROC-STATUS WHERE PROC-HANDLE = h. DISPLAY st. IF ERROR-STATUS:ERROR THEN MESSAGE ERROR-STATUS:GET-MESSAGE(1) ERROR-STATUS:GET-NUMBER(1) VIEW-AS ALERT-BOX.

DEFINE VARIABLE st AS INTEGER NO-UNDO. 
DEFINE VARIABLE h  AS INTEGER NO-UNDO. 
RUN STORED-PROC p1 h = PROC-HANDLE NO-ERROR. 
CLOSE STORED-PROC p1 st = PROC-STATUS WHERE PROC-HANDLE = h.  
DISPLAY st. 
IF ERROR-STATUS:ERROR THEN 
  MESSAGE ERROR-STATUS:GET-MESSAGE(1) ERROR-STATUS:GET-NUMBER(1) 
    VIEW-AS ALERT-BOX.

If you specified a PROC-HANDLE when you ran a stored procedure, you must specify the PROC-HANDLE when you close the stored procedure.

If you do not specify a PROC-HANDLE, the CLOSE STORED-PROCEDURE statement will close the procedure if there is only one stored procedure running. If there is more than one stored procedure running, an error will be returned.

You cannot close a send-sql-statement procedure until you have retrieved all row results.

You can close all stored procedures at once with the following statement:


RUN STORED-PROC closeallprocs.

For more information on using this statement, see OpenEdge Data Management: DataServer for ODBC and OpenEdge Data Management: DataServer for Oracle.