SQL Transformation
Use the SQL transformation to call a stored procedure or function in a Microsoft SQL Server or an Oracle database.
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.
You might use stored procedures to perform the following tasks:
- •Check the status of a target database before loading data into it.
- •Determine if enough space exists in a database.
- •Perform a specialized calculation.
- •Retrieve data by a value.
- •Drop and re-create indexes.
You can use a stored procedure to perform a calculation that you would otherwise make part of a mapping. For example, if you have a stored procedure to calculate sales tax, perform that calculation in an SQL transformation instead of re-creating the calculation in an Expression transformation.
A stored function is similar to a stored procedure, except that a function returns a single value.
When you run a mapping, the SQL transformation passes input parameters to the stored procedure. The stored procedure passes the return value or values to the output fields of the transformation.
You can map the incoming fields of an SQL transformation to the input fields of a stored procedure. The output fields in the SQL transformation consist of the stored procedure output parameters or return values. A return value is a code or text string that you define in the stored procedure. For example, a stored procedure can return a return value that indicates the date the stored procedure was run. When a stored procedure has a return value, the SQL transformation has a return value field.