:Create stored procedures

This section describes how to create stored procedures in DMS.

Prerequisites

You have access permissions to DMS and you have logged on to the DMS console.

Background

This example uses the MySQL database.

Procedure

1. Select a target database and click Log On to Database.

2. Stored procedures, functions, triggers, and events are programmable objects in DMS. In the left-side directory tree, click the Programmable Objects tab. Programmable objects are displayed,as shown below:

   

3. Click Build (Procedure). The Create Stored Procedure dialog box appears.

4. Enter a name in Stored Procedure Name. Click OK.

5. DMS provides a stored procedure template. You only need to edit the body of the stored procedure.

6. Click OK to save the stored procedure to the database. If a syntax error is found, DMS returns the cause of the error. Correct the error and click Save. DMS returns a message that indicates the stored procedure is successfully saved.

7. Click Execute to run the stored procedure.

   Example

   Set the input parameters. For example, set cnt to 80 to list the records whose values are 80.

8. Click Execute. DMS then runs the stored procedure. DMS displays the output parameters or intermediate result set, if any, of the stored procedure.

   • The Messages tab displays a message about the execution status, such as the output variables and the intermediate result set.

   • The Intermediate Result Set 1 tab displays the result set that is generated when the stored procedure is executed. If multiple result sets exist, DMS displays the same number of Intermediate Result Sets tabs numbered , 1, 2, 3, and so on.

9. Click and view the Intermediate Result Set 1 tab.

   Note

   This example displays the record whose value is 80, which is the expected result.

10. You can set the options when creating the stored procedure. Click Option to set options for the stored procedure.

11. After a stored procedure is successfully created, it is added to the Programmable Object directory tree.

    Note

    You can perform operations on the stored procedure through the following menu options:

    • Create
    • Edit
    • Delete
    • Run

12. You can run the stored procedure in the SQL window

    Note

    • To call a stored procedure, run the “call stored_procedure_name” command.
    • The SQL window displays the result set, if any, of the stored procedure.