Call Stored Procedure

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.

Note: If you want to read data into a dataflow directly from a table or view, use the Read from DB stage.

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: Connection name Enter a name for the connection. The name can be anything you choose. Database driver Select the appropriate database type. Connection options Specify the host, port, instance, user name, and password to use to connect to the database.
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. Parameters This column shows the parameters defined in the stored procedure. Stage Fields For OUT, INOUT, and RETURN parameters, this column shows the dataflow field name that will contain the data returned by the parameter. Initially, the field name is the same as the parameter name. You can modify the stage field name by clicking the field name and typing a new name for parameters. This column is not used for IN parameters. Direction One of the following: IN The parameter is an input parameter. The value you specify for this parameter is passed to the stored procedure as input. OUT The parameter is an output parameter. The stored procedure returns data to the stage in this parameter. INOUT The parameter can be used as both an input parameter to pass a value to the stored procedure, and as an output parameter to receive data returned by the stored procedure. RETURN The parameter contains a return code from the stored procedure. Types This column displays the data type of the parameter value. If the data type is not supported by Spectrum Technology Platform, the type will be "Unsupported" and the stored procedure will not execute successfully. Value In this column, enter the value you want to set for the parameter. This column is disabled for OUT parameters.
Result Set Fields	This table specifies which dataflow fields to use for the data returned by the stored procedure. Database Tables This column shows the tables from which the stored procedure returned data. Database Fields This column shows the field from which the stored procedure returned data. Stage Fields This column shows the dataflow field name that will contain the data from the database field. Types This column shows the data type of the field. If the data type is not supported by Spectrum Technology Platform, the type will be "Unsupported". Include Check the box in this column to include the field in the dataflow. If the box is not checked, the field will not be used in the dataflow.
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.