Physical Modeling › Teradata Physical Property Editors › Define Teradata Stored Procedures

Define Teradata Stored Procedures

A stored procedure is a pre-compiled collection of Transact-SQL statements that are stored under a name and processed as a unit. You can use the Stored Procedure editor to define database-level and table-level stored procedures in a Teradata physical model. The Code type determines if the stored procedure is intended to be a single procedure, generated for the entire database, or a set of procedures, generated once for each associated table.

The order in which you generate stored procedures for your database is important. The stored procedure code is generated according to the order in the Teradata Stored Procedures Editor.

To define Teradata stored procedures

1. Click Stored Procedures on the Model menu.

   The Teradata Stored Procedure Editor opens.

2. Select a stored procedure and work with the following options:

   Note: Click New on the toolbar to create a new stored procedure. Use the Enter filter text text box to filter a very large list of stored procedures to quickly locate the one you want to work with.

   Name

   Specifies the name of the stored procedure.

   Database

   Specifies the database or user to which the table belongs. Select from the drop-down list.

   Type

   Specifies the type of the object. Select from the drop-down list. Valid values are: Model Level or Table Level.

   Procedure Type

   Specifies the type of the stored procedure. Select from the drop-down list. Valid values are: SQL or External.

   Use Replace Syntax

   Lets you select whether Forward Engineering will use the REPLACE command instead of the CREATE command.

   Attach To New

   Specifies if upon creating a new (table-level only) table, this script will automatically be attached to it.

   Generate

   Generates DDL for this object during Forward Engineering.

   Generate As Pre-Script

   Specifies whether the DDL for this object must be generated as pre-script. Valid values are true or false.

3. Work with the other tabs in the editor to access additional design features, for example:
   • Work with parameters for the selected stored procedure on the Parameters tab.
   • Work with options and modes for the stored procedure on the Options tab.
   • Add a full path for source files for the selected stored procedure on the Source Files tab.
   • Add external files for the selected stored procedure on the External tab.
   • Edit the SQL code related to the selected stored procedure on the Code tab.
   • Display fully expanded code that may be shown as code fragments or partial routines on other tabs, on the Expanded tab.
   • Associate permissions with the selected stored procedure on the Permission tab.
4. (Optional) Click the Comment tab and enter any comments you want associated with the stored procedure.
5. (Optional) Click the Object Creation tab to change the database object creation order.
6. (Optional) Click the Where Used tab to view where the stored procedure is used in the model.
7. (Optional) Click the UDP tab to work with user-defined properties for the stored procedure.
8. (Optional) Click the Notes tab to view history information and view or edit user notes.
9. Click Close.

   The Teradata Stored Procedure Editor closes.

More information:

Specify Data Access Options for a Stored Procedure

Specify Source Files for a Physical Object

Specify External Files for a Physical Object

Specify Parameters for the Selected Physical Object

Edit Code in a Teradata Property Editor

View Expanded Code in a Property Editor

Add Permissions in a Teradata Property Editor

Add a Comment in a Property Editor

Change the Object Creation Order in a Property Editor

Add a UDP in a Physical Property Editor