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 to your database is important. The stored procedure code is generated according to the order in the Stored Procedures Editor.

To define Teradata stored procedures

1. Click Stored Procedures on the Database menu.

   The 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 authorization.

   Name

   Specifies the name of the stored procedure.

   Database

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

   Type

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

   Attach To New

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

   Use Replace Syntax

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

   Generate

   Generates DDL for this object during Forward Engineering.

3. Work with the following field on the General tab:

   Teradata Type

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

4. Work with the other tabs in the editor to access additional design features, for example:
   • Work with data access features for the selected stored procedure on the Data Access 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
   • Work with parameters for the selected stored procedure on the Parameters 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
   • Change the object creation order on the Object Creation Order tab
5. (Optional) Click the Comment tab and enter any comments you want associated with the stored procedure.
6. (Optional) Click the UDP tab to work with user-defined properties for the stored procedure.
7. Click Close.

   The 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

View Parameters Associated with the Selected Function

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