When you call a stored procedure from an expression, you configure the expression to return the stored procedure output values to fields in the expression. Use one of the following methods to return the output values:

Assign the output value to a local variable field.
Assign the output value to the system variable PROC_RESULT.

When you use the PROC_RESULT variable,

Data Integration

assigns the value of the return parameter directly to the output field, which you can write to a target. You can also assign one output parameter to PROC_RESULT and the other parameter to a variable.

Use expression variables to access OUT or INOUT parameters in the stored procedure. If the stored procedure returns multiple output parameters, you must create variables for each output parameter.

Use the following syntax to call a stored procedure in an expression:

If the stored procedure returns a single output parameter or return value, use the reserved variable PROC_RESULT as the output variable.

For example, the following expression calls a stored procedure called GET_NAME_FROM_ID:

:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)

inID can be either an input field in the stored procedure or a variable in the Expression transformation. When you run the mapping,

Data Integration

applies the value of PROC_RESULT to the output field for the expression.

If the stored procedure returns multiple output parameters, you must create expression variables for each output parameter. For example, if the stored procedure also returns a title, create a variable field called varTitle1 in the Expression transformation and use the field as the expression for an output field called Title. You write the following expression:

:SP.GET_NAME_FROM_ID(inID, varTitle1, PROC_RESULT)

The following image shows how you configure the Expression transformation:

Data Integration

returns output parameters in the order they are declared in the stored procedure. In this example,

Data Integration

applies the value of the first output field in the stored procedure to varTitle1 and passes it to the Title field in the Expression transformation. It applies the value of the second stored procedure output field to the output field for the expression.

The data types for the expression fields and variables must match the data types for the stored procedure input/output variables and return value.