Creating Models › Creating a Physical Model › Stored Procedures › Add a Stored Procedure for a Table or View › Define a Stored Procedure

Define a Stored Procedure

A stored procedure is a pre-compiled collection of SQL statements that are stored under a name and processed as a unit. The Stored Procedure Editor simplifies the management of model-level and table-level stored procedures. You can use the Stored Procedure Editor to add, change, view, or delete stored procedures for the model, tables, or views. You can review or assign the type of stored procedure and whether it is generated in the schema. You can also assign the Function command here.

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 Procedure Editor.

To define a stored procedure

1. Click Stored Procedures on the Model menu.

   The Stored Procedure Editor opens.

2. Select the stored procedure in the Navigation Grid that you want to define and work with the following options:

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

   Name

   Specifies the name of the stored procedure. Change the name of the stored procedure in this field.

   Type

   Specifies whether the stored procedure type is model-level or table-level. Select the type from the drop-down list.

   Attach To New

   Specifies to attach the selected stored procedure to each new table that you create. Clear the check box if you want to manually attach the stored procedure to new tables.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

3. Click the Code tab and review or assign code (including macros) to the stored procedure.
4. Click the Object Creation Order tab to specify the order of DDL generation.
5. (Optional) Click the Expanded tab to view expanded code.
6. (Optional) Click the Comment tab and enter any comments that you want to associate with the stored procedure.
7. (Optional) Click the Where Used tab to view where the stored procedure is used in the model.
8. (Optional) Click the UDP tab to work with user-defined properties for the stored procedure.
9. (Optional) Click the Notes tab to view history information and view or edit user notes.
10. Click Close.

    The stored procedure is defined and the Stored Procedure Editor closes.

Note: Click the Stored Procedure Browser button to see the tables and views that are attached to a table-level stored procedure, view the code that is used by a table-level stored procedure, and attach tables and views in a model to a stored procedure.

More information:

Define the Stored Procedures SQL Code

Review the Expanded SQL Code for a Stored Procedure

Add a Stored Procedure for a Table or View

Migrate a Stored Procedure

Attach or Detach a Teradata Table or View to a Stored Procedure

Procedure Browser