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.