Create an SQL Procedure
Go Up to Creating and Editing SQL Procedures
- In the Data Model Explorer, expand the Physical Main Model, right-click the Procedures node, and then click New Procedure.
- Complete the Procedure SQL Editor and then click OK to create the procedure.
- Once you have created the SQL procedure, you can edit it by right-clicking the procedure you want to change, and then selecting Edit SQL Procedure.
SQL tab
Enter or edit a SQL code for the procedure.
Description tab
Enter or edit a definition for the procedure. If the target database supports it, ER/Studio Data Architect adds this definition as a table comment when generating SQL code.
Permissions tab
Sets access roles and user permissions for the procedure. Keep in mind that the more specific permissions are, the more time you may have to spend maintaining and updating them. The roles and users must be previously assigned to appear in the Procedure SQL Editor. For more information, see Creating and Editing Database Roles and Creating and Editing Database Users.
Note: Against InterBase platforms, no Permissions page/tab is available.
Attachment Bindings tab
Bind an external piece of information, or attachment to the procedure. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
Notes
- You can choose to launch your preferred SQL editor when editing functions and procedures. Using the Tools tab of the Options Editor, you can enter the name and location of the SQL editor that will launch and manage the SQL ER/Studio Data Architect outputs. Choose Tools > Options > Tools > ISQL Path.
- For all new models you can choose:
- To display the schema object description or its associated symbol; choose Tools > Options > Display and then select the desired display mode.
- To hide invalid schema objects; choose Tools > Options > Schema Objects and then select Hide Invalid Schema Object Bitmap.
- For the current data model, you can choose:
- To display the schema objects or objects relationships, and object definition or object symbol; choose View > Diagram And Object Display Options > Schema Object and then select the desired display options.
- What to display when you mouse over a schema object; choose Tools > Options > Diagram > Schema Object Display and choose to display the object description or its associated DDL.
- For physical models, you can enter SQL to be applied before and after the CREATE OBJECT clause in the CREATE TABLE statement. Choose Model > Model Properties > PreSQL & Post SQL.
- The sample physical model for SQL Server 2008 in Northwind.dm1 provides some good examples of how SQL procedures can be used.