Physical Modeling › Stored Procedures › Add a Stored Procedure for a Table or View › Define a Stored Procedure

Define a Stored Procedure

You can use the Stored Procedures dialog 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.

To define a stored procedure

1. Click Stored Procedures, Model-Level or Table-Level on the Database menu.

   The Stored Procedures dialog opens.

   Note: For a Teradata DBMS, the Database menu selection is Macros, and it opens the Macros dialog.

2. Click New.

   The New Stored Procedure dialog opens.

3. Enter a name of the stored procedure and click OK.

   The stored procedure name and the first line of the stored procedure code, which can include SQL statements and macros are displayed. You can use the Display field to filter the stored procedure list to show Model-level Only, Table-level Only, or All. The order in which you add stored procedures to your model is important. The stored procedure code is generated according to the order the stored procedures display in the Stored Procedures dialog. You can change this order at any time by using the up and down arrow buttons in the Stored Procedure Browser.

4. Complete the Stored Procedure Type and Generate sections:

   Model-level

   A model-level script is executed either before or after the schema generation script.

   Table-level

   A table level script is included either before or after the CREATE TABLE statement.

   Generate

   Indicates whether the stored procedure will be generated by your database. Clear this check box to not generate the stored procedure to the schema.

   Sort

   Sorts the stored procedures in the Stored Procedures list alphabetically by stored procedures name. Appears only when you create a model-level script.

   Browser

   Opens the Stored Procedure Browser where you can attach, review, and detach stored procedures to tables and views.

   You can complete the fields on the Code, Expanded tabs and then optionally select the Comment or UDP tab to add a comment or user defined property.

   Click OK.

   Your changes are saved and the Stored Procedures dialog closes.

   Note: Click Cancel to close the dialog without saving your changes.

More information:

Define the Stored Procedures SQL Code

Define the Stored Procedures Expanded SQL Code

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