Using stored procedures

A stored procedure is a block of procedural constructs and embedded SQL statements. Use the Call procedure option under the Read method property of the Stage tab to call an existing stored procedure from a database. Use the Call procedure statement option to write your own procedure statement syntax in the text box.

Stored procedures in a source stage

Select Add procedure return value to schema to add the procedure return code to the schema as column ProcCode. The first column on the output link holds the value of the code. The procedure return code typically indicates whether the procedure succeeded or failed.

Stored procedures in a target stage

Select Execute procedure for each row to execute the procedure for each row of the input data. If you do not select Execute procedure for each row, the procedure will only execute once, for the first row. By default, Execute procedure for each row is selected.

Supporting connectors

The following connectors support stored procedures.

  • Microsoft Azure SQL Database
  • Microsoft SQL Server
  • Db2®
  • Db2 on Cloud
  • Db2 Hosted
  • Db2 Big SQL
  • Db2 Warehouse