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
  [integer-field = PROC-HANDLE ]
  [ NO-ERROR ]
  [ ( parameter[ , parameter]... ) ]

procedure

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

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

integer-field = PROC-HANDLE

Assigns a value to the specified integer field or variable (integer-field) 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

Suppresses ABL errors or error messages that would otherwise occur and diverts them to the ERROR-STATUS system handle. If an error occurs, the action of the statement is not done and execution continues with the next statement. If the statement fails, any persistent side-effects of the statement are backed out. If the statement includes an expression that contains other executable elements, like methods, the work performed by these elements may or may not be done, depending on the order the AVM resolves the expression elements and the occurrence of the error.

To check for errors after a statement that uses the NO-ERROR option:

• Check the ERROR-STATUS:ERROR attribute to see if the AVM raised the ERROR condition.
• Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if the AVM generated error messages. ABL handle methods used in a block without a CATCH end block treat errors as warnings and do not raise ERROR, do not set the ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle. Therefore, this test is the better test for code using handle methods without CATCH end blocks. ABL handle methods used in a block with a CATCH end block raise ERROR and add messages to the error object generated by the AVM. In this case, the AVM does not update the ERROR-STATUS system handle.
• Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a particular message, where message-num is 1 for the first message.

If the statement does not include the NO-ERROR option, you can use a CATCH end block to handle errors raised by the statement.

Some other important usage notes on the NO-ERROR option:

• NO-ERROR does not suppress errors that raise the STOP or QUIT condition.
• A CATCH statement, which introduces a CATCH end block, is analogous to a NO-ERROR option in that it also suppresses errors, but it does so for an entire block of code. It is different in that the error messages are contained in a class-based error object (generated by the AVM or explicitly thrown), as opposed to the ERROR-STATUS system handle. Also, if errors raised in the block are not handled by a compatible CATCH block, ON ERROR phrase, or UNDO statement, then the error is not suppressed, but handled with the default error processing for that block type.
• When a statement contains the NO-ERROR option and resides in a block with a CATCH end block, the NO-ERROR option takes precedence over the CATCH block. That is, an error raised on the statement with the NO-ERROR option will not be handled by a compatible CATCH end block. The error is redirected to the ERROR-STATUS system handle as normal.
• If an error object is thrown to a statement that includes the NO-ERROR option, then the information and messages in the error object will be used to set the ERROR-STATUS system handle. This interoperability feature is important for those integrating code that uses the traditional NO-ERROR technique with the newer, structured error handling that features error objects and CATCH end blocks.

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

An expression is a constant, field name, variable name, or 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.

If you run send-sql-statement for an SQL-based data source, you must pass a single character expression parameter containing the SQL statement you want the data source to execute.

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

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 
  (10, OUTPUT 0, OUTPUT 0) NO-ERROR.
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 of the RUN STORED-PROCEDURE statement 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-PROC 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-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

This code 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-PROC 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-PROC send-sql-statement WHERE PROC-HANDLE = h1.

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, PROC-HANDLE function, PROC-STATUS function