Use the SQL transformation to call a stored procedure or function in a relational database or to processes SQL queries midstream in a pipeline. The transformation can call a stored procedure or function, process a saved query, or process a query that you create in the transformation SQL editor.
The SQL transformation can process the following types of SQL statements:
Stored procedure or stored function
A stored procedure is a precompiled collection of database procedural statements and optional flow control statements, similar to an executable script. Stored procedures reside in the database and run within the database. A stored function is similar to a stored procedure, except that a function returns a single value.
When the SQL transformation processes a stored procedure or function, it passes input parameters to the stored procedure or function. The stored procedure or function passes the return value or values to the output fields of the transformation.
Saved or user-entered query
You can configure the SQL transformation to process a saved query that you create in Data Integration or you can enter a query in the SQL editor. The SQL transformation processes the query and returns rows and database errors.
You can pass strings or parameters to the query to define dynamic queries or change the selection parameters. You can output multiple rows when the query has a SELECT statement.