Data from a Stored Procedure using Parameters


When using Output Parameters in a Stored Procedure the behaviour of the Stored Procedure will differ with used Database brand. I.e. MS-SQL will behave differently from Oracle and DB2. This will be further explained in the 'Using the Stored Procedure in the Code' section of this page.

Defining the Stored Procedure

An example of a SELECT statement to transfer data from a stored procedure back to LION code is shown below for MS-SQL and Oracle.

MS-SQL

In MS-SQL the parameters are preceded by an @ sign before the parameter name as shown below.

The @NRROWS parameter will return the number of rows that is selected by the WHERE clause. This is needed for the MS-SQL code and will be explained in the 'Using the Stored Procedure in the Code' section of this page.

Oracle (PL/SQL)

In PL/SQL the parameters to move the results to are named after a separate INTO keyword as shown below.

The corresponding Parameters definitions should then be for both MS-SQL and Oracle.

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

MS-SQL


The most important thing to realize that, when using output parameters in a stored procedure, these parameters can only return a single result. I.e. when the conditions in a WHERE clause will lead to multiple records in the result set, in MS-SQL only the last record (following the order of an index or ORDER BY clause) will be returned in the output parameters.

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

gcp : storedproc (stp_custs_params)

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

gcp.parcountry := 'Nederland'

A stored procedure with the use of output parameters will always return a single result in the output parameters. A loop command can not be used and instead the <handler>.execute() command should be used. The output parameters can then be returned using the syntax <handler>.<output parameter> as shown in the example below.

gcp.execute()
sme('Nr of records in result set:', gcp.nrrows)
sme('Firstname:', gcp.resfirst)
sme('Lastname:', gcp.reslast)
sme('City:', gcp.rescity)

The output parameter nrrows can be used to detect whether the result set (of which only the last record is returned) will contain zero records, a single record or multiple records.

Oracle


The most important thing to realize that, when using output parameters in a stored procedure, these parameters can only return a single result. I.e. when the conditions in a WHERE clause will lead to multiple records in the result set, Oracle will see this as an error and generate an exception.

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

gcp : storedproc (stp_custs_params)

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

stpar.parcountry := input('Enter the country for the select')

A stored procedure with the use of output parameters will always return a single result in the output parameters. Oracle checks on this more strict than MS-SQL and will return an error when the result set contains more anything different than one single record.

stpar.execute ()
if not resok
sme('ERROR:', getlastresult)
else
sme('Firstname:', stpar.resfirst)
sme('Lastname:', stpar.reslast)
sme('City:', stpar.rescity)
endif