Syntax

You can use the specific syntax to run stored procedures for your database.

Default syntax

Stored procedure calls use ? as a parameter marker. The following syntax calls a stored procedure with name <procedure_name> on an input parameter @<column_name> and output column ?, both in parentheses.

CALL my_procedure(@<column_name>, ?)

If the input parameter is a string, enclose the name in quotation marks.

? = CALL my_procedure('@<string_column_name>', ?)

See the following syntax for connectors with Date, Time, or Timestamp input parameters. The last parameter in the example is the output parameter.

CALL my_procedure('@date_column', '@time_column', '@timestamp_column', ?)

If the stored procedure is called in a stage with only output links, then ? always indicates an output parameter. If the stored procedure is called in a stage with only input links, then ? always indicates an input parameter.


CALL my_procedure()
CALL my_procedure(?, ?)
CALL my_procedure(4,?)

Oracle syntax

See the following syntax for the Oracle connector without date, time, or timestamp input parameters. The last parameter is an example of the output parameter.

CALL my_procedure(@integer_column, '@string_column', ?)

When you call a stored procedure for the Oracle connector that contains date, time, or timestamp input parameters, you must use a ? placeholder for the parameters. Use the placeholder ? when connector is configured in a transform mode. Do not use @column_name syntax and provide column values through the parameter binding instead.

Correct syntax:

CALL my_procedure(?, ?, ?)

Incorrect syntax:

CALL my_procedure(@date_column, @time_column, @timestamp_column)

Microsoft SQL Server, Microsoft Azure SQL Database, SAP ASE syntax

See the following syntax to form a call procedure statement for Microsoft SQL Server, Microsoft Azure SQL Database, or SAP ASE connectors. For any type of procedure, add the ? = in front of how you normally create the call procedure statement. The procedure return code is captured in ? and sent to the output column.

? = CALL my_procedure(@column_name, ?)

For Microsoft SQL Server with different database versions, see the following syntax for call procedure statements. Indicate the version number with ;<version_number> after the procedure name. Standard input and output parameter syntax applies.

? = CALL testprocedure;3(?)

Transform mode

For stored procedures that require both input and output parameters, specify the input parameter with @<column_name>, and the output parameter ?. For example, if the procedure requires 2 input values: input_1, input_2, and 2 output values: output_1 and output_2, the syntax for the call statement is as follows.

CALL my_procedure(@input_1,@input_2,?,?)

For Microsoft SQL Server, Microsoft Azure SQL Database, SAP ASE connectors, add ? = in the front.

? = CALL my_procedure(@input_1,@input_2,?,?)