The ABL statement RUN STORED–PROC allows you to run a stored procedure in the ODBC data source. You must also indicate the end of a stored procedure by using the CLOSE STORED–PROC statement.
This is the syntax for the RUN STORED–PROC statement:
This is the partial syntax for the CLOSE STORED–PROC statement:
Syntax
CLOSE STORED-PROC procedure
For example, the following ABL code runs the stored procedure pcust:
DEFINE VARIABLE handle1 AS INTEGER NO-UNDO.
RUN STORED-PROC pcust handle1 = PROC-HANDLE (20, OUTPUT 0, OUTPUT 0).
FOR EACH proc-text-buffer:
DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle1.
DISPLAY pcust.orders pcust.states.
This code first defines an integer variable named handle1 that serves as a handle for identifying the stored procedure. If you have only one active stored procedure, you do not have to specify a handle. However, it is good programming practice to use handles to identify all of your stored procedures.
It then runs the stored procedure as follows:
The pcust stored procedure passes the values 20, 0, and 0 to the three parameters (specifying orders and states as output parameters).
Using a FOR EACH statement, it reads the results into the Progress-supplied buffer proc–text–buffer.
It displays the results.
The Progress procedure next uses the CLOSE STORED–PROC statement to fetch the orders and states output parameters and then displays them. Note that the stored procedure does not return output parameter values unless you request them with the keyword OUTPUT or INPUT–OUTPUT when you execute the procedure.
You can close all stored procedures at once with the following statement:
RUN STORED-PROC closeallprocs.
Note: For Sybase, the DataServer typically maintains one connection. If your application requires that you process other queries while a stored procedure is open, use the -Dsrvqt_separate_connection parameter or the QUERY–TUNING (SEPARATE–CONNECTION) option to specify that the DataServer use a separate connection for each statement that requires a cursor.
See Stored Procedure Reference for a description of the complete syntax for the Progress statements and functions that support running stored procedures.
The pcust stored procedure passes the values 20, 0, and 0 to the three parameters (specifying orders and states as output parameters).