You can configure an SQL transformation to call a stored procedure in Amazon Redshift. The stored procedure must exist in the Amazon Redshift database before you create the SQL transformation. When the SQL transformation processes a stored procedure, it passes input parameters to the stored procedure. The stored procedure passes the return value to the output fields of the transformation.

The following image shows a stored procedure in a SQL transformation:

Consider the following rules and guidelines for stored procedures:

When the incoming fields from a stored procedure are of char or varchar data type, the SQL transformation applies a default precision of 256 for these fields, regardless of the precision defined at the database endpoint.
When you use the same column names in the source table as in the table used in the stored procedure, the mapping task fails.
When you specify the numeric data type, for example,
numeric(10,2)
for a column in a stored procedure, the mapping task fails.
When you create a stored procedure in a mapping, the output port must not include the refcursor data type.
You cannot read from multiple stored procedures that have the same name even if the input parameters that you specify for the stored procedures are different.

For more information about stored procedures, see

Transformations

in the Data Integration documentation.