Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## Interfacing with RDBMS stored procedures

> 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 Progress. 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 the 4GL 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.

> Example 3–3 shows a stored procedure with multiple result sets, output parameters, and a 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

> Example 3–3: A stored procedure with multiple result sets, output parameters, and a single return value

> 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 Progress 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.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095