Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
RDBMS Stored Procedure Details : RDBMS stored procedure basics
 

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 20. 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 RunStored-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. The table below lists issues that occur when you pass certain data types as parameters.
Table 21. 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.
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.