User Guide > User Guide > Working with Databases > Working with Procedures, Sequences, Events, and Synonyms > Procedures
Was this helpful?
Procedures
A stored procedure is a named set of SQL statements that is stored in the database in compiled form so that a number of programs can share it.
Using Director, you can create or delete user procedures. You can also set permissions and view their properties.
System procedures are generated internally by the database to support database objects such as constraints and foreign keys. System procedures cannot be created, modified, or deleted. They are stored in a separate folder under the Procedures folder.
Note:  Procedures are not available for Actian Vector.
Procedure Dialog
The Database Procedure dialog contains the following pages:
General
Lets you specify a name for the procedure and enter the queries to be executed when the procedure is run. Procedure names must follow Object Naming Rules.
Parameters
Lets you specify parameters to use in the procedure. Right-clicking a table row and selecting Insert Parameter creates a new parameter. For each parameter, you may specify:
• Parameter Name (see Object Naming Rules)
• Data Type
• Param Type (in, out, or inout)
• Allow Nulls
• Default
To delete a parameter, right-click on its table row and select Delete Parameter from the context menu.
Declared Variables
Lets you declare variables to use in the procedure. Right-clicking a table row and selecting Insert Variable creates a new variable. For each variable, you may specify:
• Variable Name (see Object Naming Rules)
• Data Type
• Allow Nulls
• Default
To delete a variable, right-click on its table row and select Delete Variable from the context menu.
For more information, see Create a Procedure.
Create a Procedure
Before you can create a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).
Note:  Procedures are not available for Actian Vector.
You create procedures using the New Database Procedure dialog (see Procedure Dialog). You can open this dialog the following ways:
• From the context menu of a database’s Procedure folder or procedure node: New Procedure
• On the main menu ribbon when a Database node is selected in the Instance Explorer: Database, Procedure, New Procedure (see Database Tab)
To create a database procedure
1. Open the New Database Procedure dialog in one of the ways explained previously.
2. On the General page, specify a name for the procedure (see Object Naming Rules).
3. In the text box, enter the queries to be executed when the procedure is called.
The “BEGIN” and “END” markers are provided to indicate which parts of the procedure definition should be entered in the text box.
4. Click the Parameters link in the “Select a page” portlet.
5. In the first empty row of the Database Procedure Parameters table, right-click and select Insert Parameter from the context menu.
6. Specify a parameter name, select a data type and a parameter type, choose whether nulls are allowed, and select whether the parameter type is the default type.
7. To add more parameters, repeat Steps 5 and 6.
To delete a parameter, right-click on the row and select Delete Parameter from the context menu.
8. Click the Declared Variables link in the “Select a page” portlet.
9. In the first empty row of the Parameter Variable Declarations table, right-click and select Insert Variable from the context menu.
10. Specify a variable name, select a data type, choose whether nulls are allowed, and select whether the data type is the default type.
11. To add more variables, repeat Steps 9 and 10.
To delete a variable, right-click on the row and select Delete Variable from the context menu.
12. When you are finished, click OK on the General page.
The procedure is created and stored in the Procedures folder of the database.
Modify a Procedure
Before you can modify a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).
When you modify a procedure, Director creates SQL to drop and create the original stored procedure and places it in a new tab in the Multiple Document Interface. You may modify the CREATE PROCEDURE statement, execute it, or save the edited SQL to a file.
You modify a procedure with the Modify Procedure document. You can open this document the following ways:
• From the context menu of a database’s procedure node: Modify
• On the main menu ribbon when a sequence node is selected in the Instance Explorer: Database, Procedure, Modify Procedure (see Database Tab)
To modify a database procedure
1. In the Instance Explorer, right-click the procedure node and select Modify from the context menu.
A new tab is opened in the Multiple Document Interface, displaying the SQL text of the procedure.
2. Edit the query text.
3. Execute and/or save the statements to a file (see Save a Query).
Delete a Procedure
Before you can delete a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).
You delete procedures using the Delete Procedure dialog. You can open this dialog the following ways:
• From the context menu of a procedure node: Delete
• On the main menu ribbon when a procedure node is selected in the Instance Explorer: Database, Procedure, Delete Procedure (see Database Tab)
To delete a database procedure
1. Open the Delete Database Procedure dialog in one of the ways explained previously.
The Delete Procedure dialog appears.
2. Click OK.
The procedure is removed from the Procedures folder of the database.
View Procedure Properties
Before you can view the properties of a database procedure, the installation containing the database must be connected in the Instance Explorer (see Connect to an Actian Database Instance).
You view procedure properties using the Procedure Properties dialog. You can open this dialog the following ways:
• From the context menu of a procedure node: Properties
• On the main menu ribbon when a procedure node is selected in the Instance Explorer: Database, Procedure, Procedure Properties (see Database Tab)
To view the properties of a database procedure
1. Open the Procedure Properties dialog in one of the ways explained previously.
The name and owner of the procedure are displayed.
2. Click the page links to view parameters and the procedure definition.
3. Click specific properties to display their descriptions at the bottom of the dialog.
You may copy the procedure definition by clicking in the text area, selecting all text, and then clicking Copy.
4. Click OK to close the dialog.
Last modified date: 06/06/2024