You use the RUN STORED-PROC statement with the send-sql-statement option and pass the Transact-SQL statements as a parameter. The syntax of the statement must be valid Transact-SQL syntax. The following example shows how this code passes a SELECT statement as a parameter.
Note: The SQL statement(s) passed to the send-sql-statement stored procedure is not limited to the 32,000 bytes of a OpenEdge CHARACTER field. To pass SQL of greater size, store the SQL text in a LONGCHAR field and pass that as a parameter to the send-sql-statement stored procedure.
Passing a SELECT statement as a parameter
DEFINE VARIABLE handle1 AS INTEGER NO-UNDO.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT name, address, city, state, postal_code
FROM customer WHERE credit_limit >= 500").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.
The code above returns the name, address, city, state, and postal_code for all customers whose max-credit is greater than or equal to $500. You must read the results into a buffer as you would with a stored procedure called by an OpenEdge procedure. You can read the results into the proc-text-buffer defined by OpenEdge as shown in the example above. Alternatively, you can define your own buffer from within your data source that can accept other data types as well as the CHARACTER data type.
