Call Stored Procedure is a source stage that executes a stored procedure in a database, and returns the results of the stored procedure call as input for the dataflow. Use Call Stored Procedure when you want to get data from a database using a database's stored procedure rather than a query to a table or view.
You may want to use Call Stored Procedure to read data into a dataflow if you have business logic embedded in the stored procedure and you want to use that logic in your Spectrum Technology Platform environment. For example, many operational systems do not use referential integrity checks in the database for large constantly-updated tables because of the reduction in performance that such checks would cause. So to maintain referential integrity, you could create stored procedures and use them for all updates to the system.
Stored procedures can also be used to simplify management of the Spectrum Technology Platform environment. For example, if you have hundreds of ETL processes that all read the same data, you may want to put the query into a stored procedure so it is in one place. This makes maintenance easier since you only need to modify the one stored procedure instead of hundreds of different processes.
Option Name |
Description |
---|---|
Connection |
Select the database connection you want to use. Your choices vary depending on what connections are defined in the Connection Manager of Spectrum Management Console. If you need to make a new database connection, or modify or delete an existing database connection, click Manage Connections. If you are adding or modifying a database
connection, complete these fields:
|
Schema |
Specifies the schema that contains the stored procedure you want to call. |
Procedure |
Specifies the stored procedure you want to call. |
Stored Procedure Parameters |
This table specifies the values for the stored procedure parameters.
|
Result Set Fields |
This table specifies which dataflow fields to use for the data returned by the stored procedure.
|
Get Fields |
Click this button to populate the Result Set Fields table with the result set schema returned by the stored procedure. This will execute the stored procedure and get the result set schema. |
Add |
Click this button to add a result set field manually. |
Remove |
Click this button to remove a result set field from the list of available fields. |