RUN STORED-PROCEDURE statement

Runs a non-ABL stored procedure or allows you to send SQL to an SQL-based data source using an OpenEdge DataServer.

Syntax

RUN STORED-PROCEDURE procedure-name
     [[ LOAD-RESULT-INTO handle [ status = PROC-STATUS ]] | 
     [ identifier = PROC-HANDLE]] 
     [ NO-ERROR ]
     [ ( parameter [ , parameter ] . . .  ) ]

Note that not all options shown can be used simultaneously.

procedure-name

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

Use send-sql-statement to send an SQL statement to an SQL-based data source.

LOAD-RESULT-INTO handle

Loads the results using a handle which points to one or more temp-tables. Note that the handle can be defined as an EXTENT. This enables you to pass more than one temp-table handle in those instances where SQL statement(s), or named stored procedures, return more than one result set. You can pass an unprepared temp-table (that is, no defined schema) with LOAD-RESULT-INTO. In such cases, the temp-table structure is prepared dynamically. If the temp-table defined by the handle already has a prepared schema, the temp-table structure must match the structure of the result set to avoid data mismatch errors. See the "Data types" section in the "Initial Programming Considerations" of your respective DataServer Guide for data mapping requirements. If multiple result sets are returned, each result set needs its own temp-table mapping. The elements of a temp-table extent should be arranged in the same order that the procedure returns its sets of results.

LOAD-RESULT-INTO carries an implicit CLOSE STORED-PROCEDURE statement.

If handle does not point to a temp-table, a run-time error occurs.

status = PROC-STATUS

Assigns an integer value to status containing the return status from the stored procedure. The status is typically a code indicating whether the procedure succeeded or failed and why.

identifier = PROC-HANDLE

Assigns a value to identifier, of the appropriate data type (usually INTEGER), that uniquely identifies the stored procedure returning results from the non-OpenEdge database or that uniquely identifies the SQL cursor used to retrieve results from an SQL-based data source, such as a DataServer for Microsoft SQL Server.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

Note: This option must appear before any run-time parameter list.

parameter

A run-time parameter to be passed to the stored procedure. A parameter has the following syntax:

[ INPUT | OUTPUT | INPUT-OUTPUT ] [ PARAM parameter-name = ] expression ) ]

INPUT is the default. OUTPUT and INPUT-OUTPUT parameters must be record fields or program variables. For ORACLE, OUTPUT and INPUT-OUTPUT work the same way.

parameter-name is the name of a keyword parameter defined by the stored procedure. If not specified you must supply all the parameters in the correct order and you must precede your assignment statement with the PARAM keyword. If you do not supply a required parameter, and no default is specified in the stored procedure, a run-time error occurs.

expression is a constant, field name, variable name, or expression. If you use send-sql-statement for an SQL-based data source, you must pass only one character expression containing the SQL statement you want the data source to execute.

Examples

This procedure runs the ORACLE stored procedure pcust and writes the results of the stored procedure into the ABL-supplied buffer, proc-text-buffer. The same code works for accessing a stored procedure from a DataServer for Microsoft SQL Server data source.

DEFINE VARIABLE intvar AS INTEGER NO-UNDO.

RUN STORED-PROCEDURE pcust intvar = PROC-HANDLE NO-ERROR
  (10, OUTPUT 0, OUTPUT 0).
FOR EACH proc-text-buffer WHERE PROC-HANDLE = intvar:
  DISPLAY proc-text-buffer.
END.
IF ERROR-STATUS:ERROR THEN
  MESSAGE "Stored Procedure failed to run".
ELSE 
  CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = intvar.

This procedure uses the send-sql-statement option to send SQL to ORACLE. It writes the results of the stored procedure into the ABL-supplied buffer, proc-text-buffer. The same code works for sending SQL to a DataServer for Microsoft SQL Server data source:

DEFINE VAR handle1 AS INTEGER.

RUN STORED-PROCEDURE send-sql-statement handle1 = PROC-HANDLE
  ("SELECT name, cust_num FROM customer").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
  DISPLAY proc-text.
END.

CLOSE STORED-PROCEDURE send-sql-statement WHERE PROC-HANDLE = handle1.

This example shows how to trap errors from the non-OpenEdge RDBMS within a procedure:

DEFINE VAR h1 AS INTEGER NO-UNDO.
DEFINE VAR jx AS INTEGER NO-UNDO.

RUN STORED-PROCEDURE send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from xxx.customer where name between 'A' and 'Z' ").
IF ERROR-STATUS:ERROR THEN
DO jx = 1 TO ERROR-STATUS:NUM-MESSAGES:
  MESSAGE "error" ERROR-STATUS:GET-NUMBER(jx)
    ERROR-STATUS:GET-MESSAGE(jx).
END.

CLOSE STORED-PROCEDURE send-sql-statement WHERE PROC-HANDLE = h1.

This example shows how to use the send-sql-statement with the LOAD-RESULT-INTO option. Note that the temp-table created for tthndl is unprepared. When tthndl is given to LOAD-RESULT-INTO and the temp-table is unprepared, LOAD-RESULT-INTO prepares the temp-table when results are returned. Note also that you do not have to specify a CLOSE STORED-PROCEDURE statement, since it's done implicitly with LOAD-RESULT-INTO.

DEFINE VARIABLE res    AS INTEGER NO-UNDO INITIAL 0.
DEFINE VARIABLE tthndl AS HANDLE.

CREATE TEMP-TABLE tthndl.

RUN STORED-PROCEDURE send-sql-statement LOAD-RESULT-INTO tthndl
  res = PROC-STATUS ("SELECT * FROM customer").
  
IF (res = 1) THEN 
  DISPLAY "Succeeded".

Notes

• The RUN STORED-PROCEDURE statement starts a transaction with the same scope as transactions started with the UPDATE statement.
• For more information on using this statement and on using the built-in procedure names, send-sql-statement, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server and OpenEdge Data Management: DataServer for Oracle).

See also

CLOSE STORED-PROCEDURE statement, NO-ERROR option, PROC-HANDLE function, PROC-STATUS function