RDBMS stored procedure basics

In the OpenEdge environment, you can think of a stored procedure definition as having two basic, interrelated parts:

  • Execution controls to run and close a stored procedure — Comprises the information needed to execute a stored procedure request against the MS S data source. At a minimum, all stored procedures discussed in this guide are assessable using the RUN STORED-PROCEDURE statement.
  • Language elements that enable access to specific data results — Qualifies the retrieved data, or result sets, that the stored procedure's execution returns. Various keywords, phrases, statements, and syntax elements support different retrieval options for stored procedure output. This part of the stored procedure implementation reflects your analysis of your data needs; based on this analysis, you determine the additional syntax elements you need to define the output elements and data results you want retrieved.

The following table identifies and briefly introduces the elements that comprise a stored procedure definition; each of these elements is also more fully discussed later in this chapter.

Table 1. Stored procedure language elements
ABL language element Description
RUN STORED-PROCEDURE statement Executes a stored procedure
PROC-HANDLE phrase Allows you to specify a handle to identify a stored procedure
PROC-STATUS phrase Reads the return value
LOAD-RESULT-INTO phrase Allows data from a result set that is returned for a foreign data source to be put into one or more temp-tables
PARAM phrase Identifies run-time parameters to be passed to and/or from the stored procedure
CLOSE STORED-PROCEDURE statement Enables the values to be retrieved from the output parameters that you defined for the stored procedure, finalizes result sets data processing, and tells OpenEdge that the stored procedure has ended
NO-ERROR phrase Allows native database errors (that occur during stored procedure execution) to be evaluated and handled by the application.
Note: This can also be achieved by a CATCH end block in the stored procedure.

For more information on handling errors using the NO-ERROR option or a CATCH end block, see Handling errors.
Note: You can substitute the abbreviations RUN STORED-PROC and CLOSE STORED-PROC for the full names RUN STORED-PROCEDURE and CLOSE STORED-PROCEDURE, respectively. The remainder of this guide generally uses the abbreviated form.

See Run Stored-Procedure details for more details about the reference entries presented in the previous table.

As previously noted in the table above, you can pass data types in the RUN STORED-PROCEDURE statement using the PARAM phrase. See MS SQL Server data type equivalents in OpenEdge for supported data types with stored procedures. The table below lists issues that occur when you pass certain data types as parameters.

Table 2. Argument data types for stored procedures
OpenEdge MS SQL Server data source

DECIMAL

INTEGER

INT64

 The DataServer converts each of these data types in the schema image to the equivalent default OpenEdge data type as follows:
  • DECIMAL=DECIMAL(default) , or Float
  • INTEGER=INTEGER
  • INT64=BIGINT
However, you can use the Data Dictionary to update the data type and format information in the field property sheet for the parameter.
CHAR The data source represents this type as a VARCHAR parameter. Its size cannot exceed the VARCHAR size limit for the associated data source. If the VARCHAR parameter exceeds this limit, it causes an error.
DATE If you pass a DATE data type as an input parameter and use it in an equality test, the test might fail. In this case, use DATEPART() or DATEDIFF() in the Transact-SQL of your native stored procedure to isolate parts of the date structure for which you might want to test.
DATETIME You can specify a DATETIME data type in a temp table used to receive results from a stored procedure using the LOAD-RESULT-INTO phrase.
MEMPTR You can specify a MEMPTR data type in a Param phrase as an INPUT or an OUTPUT parameter to receive results from a corresponding BLOB data type parameter to or from a RUN STORED-PROCEDURE statement.
LONGCHAR You can specify a LONGCHAR data type in a Param phrase as an INPUT or an OUTPUT parameter to receive results from a corresponding CLOB data type parameter to or from a RUN STORED-PROCEDURE statement.

Note these stored procedure points:

  • Input and output parameters are displayed as fields.
  • Stored procedures called from within OpenEdge applications cannot return Boolean values to LOGICAL data types.
  • If you are running several stored procedures, run them serially and process all the results from one stored procedure and close the procedure before you run a second one. By default, the DataServer allows one active request for running a stored procedure. It is not necessary to specify the PROC-HANDLE phrase when procedures are run serially.

When you run stored procedures concurrently, the DataServer uses one connection to the data source per procedure. If different stored procedures attempt to update the same record from a single client's requests, the connections could block each other or a deadlock might occur.

Note:

You must define a PROC-HANDLE phrase for each stored procedure phrase that is simultaneously active. This technique provides a CLOSE STORED-PROC statement that can identify the targeted open procedure and close it.

In contrast, since a stored procedure executed with the LOAD-RESULT-INTO phrase implicitly closes the procedure once the execution ends and the data retrieved is placed into temp tables, it essentially runs serially and has no use for a PROC-HANDLE.

  • When you create or update your schema image, the stored procedures appear in the list of accessible objects along with tables, view, and sequences. OpenEdge allows you to run the stored procedures that you create in data sources using the procedure definitions in your schema image. See your Microsoft SQL Server documentation for complete information about creating and using stored procedures.
  • If the ABL that executes a stored procedure is already within a transaction block, the stored procedure becomes an extension of that transaction and will not commit to the database until the ABL transaction is completed. However, because the stored procedure does not execute as part of ABL client process, it cannot be rolled back by ABL.
  • The DataServer cannot roll back sub-transactions in the stored-procedure context since it has no control over what the stored procedure executes.

The following section expands on the use of the RUN STORED-PROC statement.