Data from a Stored Procedure using Fields

Due to limitations of an Oracle database, (output) fields can only be used with MS-SQL and DB2 databases

 

Defining the Stored Procedure

The newest and preferred method to transfer data from a stored procedure back to LION code is by defining the SQL command string that will select data from one or more columns of a table and then define (output) fields with types and names that match the (alias) column names . The SQL Code could then be as shown in the example below.

 
The Fields definitions for the example should then be (where the type and length should match the table columns).

 
In the Parameters definitions the parameter @PARCOUNTRY should be defined as shown in the help page Data to a Stored Procedure.

Before the Stored Procedure can be used it has to be checked in, generated and imported into the database by performing a Reorganize on the database.

 

 

Using the Stored Procedure in the Code

Of course first a stored procedure handler has to be defined in the Local Definitions section:

 

 

Next any input parameters used in the stored procedure have to be set before execution.

 

 

A stored procedure that returns data with the use of (output) fields, will always return a result set. The loop instruction will first execute the stored procedure and then loop through the returned result set, record by record. The column values of the record can be returned using the syntax <handler>.<field name> as shown in the example below.