As previously mentioned, the RUN STORED-PROCEDURE statement is the initial statement required for the execution of all stored procedures you define and initiate through OpenEdge. It can run an RDBMS stored procedure bound by the data source, or allow you to send SQL to a MS SQL Server data source using an OpenEdge DataServer. The specific, additional keywords and elements you must include in a stored procedure or define for a send-sql-statement depend on the stored procedure characteristics including its signature, data results, and methods chosen to retrieve output to an ABL procedure.
This section identifies and describes the syntax elements, keywords, and other requirements associated with obtaining stored procedure output from a stored procedure data request.
The following example shows a stored procedure with multiple result sets, output parameters, and a return value.
A stored procedure with multiple result sets, output parameters, and a single return value
/* pcust stored procedure*/
CREATE PROCEDURE pcust (@num INT, @orders INT OUT, @states INT OUT) AS
BEGIN
SELECT customer.cust_num, customer.name, order_num FROM customer, order_
WHERE customer.cust_num = order_.cust_num AND customer.cust_num > @num
SELECT @orders = @@rowcount
SELECT cust_num, state.state FROM customer, state WHERE
customer.state = state.state AND customer.cust_num > @num
SELECT @states = @@rowcount
END
RETURN 0
This Transact-SQL code creates the stored procedure pcust and defines three parameters: num, which is an input parameter (the default parameter type), and orders and states, which are output parameters. The procedure returns values for the output parameters to the caller after processing the results of the pcust SELECT statements. You can think of output parameters as temporary fields associated with a record buffer defined by the procedure definition in the OpenEdge schema image; that is, you can access the data in these columns using the standard notation of tablename.fieldname. (Note that although pcust is a stored procedure, it is stored as a table definition.) For example, you can access the data in the orders and states fields by specifying pcust.orders and pcust.states. All the parameters in the example have an INTEGER data type.
