Data from a Stored Procedure (basic)

Defining the Stored Procedure

The most simple method to transfer data from a stored procedure back to LION code is by only defining the SQL command string that will select data from one or more columns of a table. E.g. with a SELECT as shown (with only input parameter(s)).

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 without the use of output parameters 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>.as<type>('<column name>') as shown in the example below.

The three allowed types are:

• Integer (asinteger)
• Numeric (asnumeric)
• String (asstring)

And the used type should match the type of the table column of course.