Syntax

CREATE VIEW _BUFFER_buffer-name

Views created in your MS SQL Server data source

The following code examples show two views created in your MS SQL Server data source that you can use as buffers to store the results from the stored procedure pcust.

CREATE VIEW _BUFFER_pcust_orders AS SELECT customer.cust_num, 
  customer.name, order_num FROM customer, order_ WHERE 1 = 0
GO

CREATE VIEW _BUFFER_pcust_states AS SELECT cust_num, state.state 
  FROM customer, state WHERE 1 = 0
GO

Result set of pcust - typed buffers

The following code example shows ABL procedure results of the previous stored procedure pcust as it is written into the new buffers pcust_orders and pcust_states.

/* Results of the first two example code examples - Typed buffers */

RUN STORED-PROC pcust (20, output 0, output 0).
FOR EACH pcust_orders: 
  DISPLAY pcust_orders.
END.
FOR EACH pcust_states:
  DISPLAY pcust_states. 
END.
CLOSE STORED-PROC pcust. 

DISPLAY pcust.orders pcust.states.

Because two different buffers have been defined, the returned values maintain their data types instead of being converted to character strings and stored in the OpenEdge-defined buffer proc-text-buffer. You can then use the returned values in calculations without first converting them back to their original data types. In addition, the two separate buffers make your output look cleaner, allowing ABL to build a new default frame for the two different types of output. Reading your results into an explicitly defined buffer also allows you to manipulate the data just as you would manipulate data from an OpenEdge database; for example, with Frame phrases and FORM statements.

Procedure handles

The following example accesses the stored procedure pcust twice; procedure handles (through the PROC-HANDLE function) identify the different results from your data source.

/* Procedure handles */
DEFINE VARIABLE handle1 AS INTEGER NO-UNDO.
DEFINE VARIABLE handle2 AS INTEGER NO-UNDO.

RUN STORED-PROCEDURE pcust handle1 = PROC-HANDLE (20, output 0, output 0).
RUN STORED-PROCEDURE pcust handle2 = PROC-HANDLE (20, output 0, output 0).

FOR EACH pcust_orders WHERE PROC-HANDLE = handle1:
  DISPLAY pcust_orders.
END.
FOR EACH pcust_states WHERE PROC-HANDLE = handle1:
  DISPLAY pcust_states.
END.
FOR EACH pcust_orders WHERE PROC-HANDLE = handle2:
  DISPLAY pcust_orders.   
END.
FOR EACH pcust_states WHERE PROC-HANDLE = handle2:
  DISPLAY pcust_states.
END.

CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle1.
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = handle2.

The results look the same as in the first two examples. However, because you are running a stored procedure twice, ABL uses the procedure handles to identify the different instances. If you have more than one stored procedure open simultaneously in your application, you must explicitly define procedure handles for each.

Joining stored procedure results with other database tables

The following example shows how to use standard OpenEdge syntax to join the results from a stored procedure with other tables in the database.

/* Join with procedure results */
RUN STORED-PROC pcust (20, output 0, output 0).
FOR EACH pcust_orders, EACH orderline 
  WHERE pcust_orders.ordernum EQ orderline.ordernum:
  DISPLAY orderline.ordernum orderline.itemnum.
END.
FOR EACH pcust_states:
  DISPLAY pcust_states.
END.
CLOSE STORED-PROC pcust.

The example code joins the order information returned from the stored procedure with the orderline information in the same database.