Call Stored Procedure (any) (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

This function is only available when connected to a Teradata database.

Stored Procedures, whether SQL Stored Procedures or External Stored Procedures, act as programs that may take or return parameters, return data sets, and/or create tables or views in the database. They may be user-defined or system-supplied. When moving a Call Stored Procedure element onto a Run Unit, the following tree element is created.

Variable Creation > Input > Variables: SQL Elements pane - Run Units > Call Stored Procedure

At the same time, a Properties dialog is displayed so that a stored procedure can be selected and argument descriptions can be viewed.

Variable Creation > Input > Variables: SQL Elements pane - Run Units > Call Stored Procedure Properties

  • Database Name — Enter the database name where the Stored Procedure resides. Choices in the pull-down list include various system supplied databases such as SYSLIB plus the databases specified in the Connection Properties dialog. A name may also be typed into the selector.
  • Procedure Name — Enter or select the name of the Stored Procedure.
  • Description — Information about the arguments, if any, is displayed in this read-only field after a Stored Procedure has been selected.
  • Expert Options
    Variable Creation > Input > Variables: SQL Elements pane - Run Units > Expert Options Properties

    • Ignore Output Parameters — If this option is selected, no attempt is made to store the values of Output or Input-Output parameters in Literal Parameter SQL elements.
  • Generate Arguments — This option is available only when the Arguments folder under the Stored Procedure element is empty. For Stored Procedures, Input arguments are generated as Literal elements of the appropriate type (string, number, date etc.), while Input-Output and Output arguments are generated as Literal Parameter elements of the appropriate type so that they may receive the final value of the argument after execution.
    The selection of the Generate Arguments option is only temporary and will revert to being unchecked the next time the dialog is displayed.
  • Number of Arguments — This field is supplied based on information in the data dictionary. If the user changes this field to a value less than the number of arguments displayed on the Description tab and also checks the Generate Arguments option, fewer arguments will be generated but the SQL generated will contain the value NULL for each missing trailing argument. If Number of Arguments is set to a greater value it will have no effect on the arguments generated.
  • Omit Database In Call — When checked, this option causes the procedure name to appear without the containing database name preceding it in the SQL generated for this Stored Procedure Call. In this case, upon calling the Stored Procedure in generated SQL, the database software will first search for the Stored Procedure in the current database (either the default database of the data source connection or the last issued DATABASE command) and, if necessary, in standard system databases (for example, SYSLIB, TD_SYSFNLIB and SYSUDTLIB).
    The changed value for Number of Arguments is only temporary and will revert to the true value the next time the Properties dialog is displayed.