Modeling Stored Procedures in ERD

A stored procedure is a pre-written procedure code that allows you to execute over and over again for validation or quick retrieval of data. The use of stored procedure helps maintain a consistent implementation of logic across program modules and applications. It also makes the design, coding and testing easier because the logic is put in a single place - the stored procedure.

In Visual Paradigm, stored procedure is modeled in form of a procedure container. You can create stored procedures (mind the 's' here) shape, and add stored procedure to the procedures shape as rows in the procedures shape.

Creating a Stored Procedure

  1. Select Stored Procedures from diagram toolbar.
    Select Stored Procedures
    Select Stored Procedures
  2. Click on the diagram to create a stored procedures shape. Note again that this is a container of stored procedures, not the procedure itself.
  3. Enter its name.
    Stored procedures created
    Stored procedures created
  4. To create a stored procedure, right click on the stored procedures shape and select New Procedure from the popup menu.
  5. Enter the physical name of the procedure .
    Stored procedure added
    Stored procedure added
  6. Right click on the procedure and select Open Specification... from the popup menu.
  7. Enter the Create statement of procedure. The create statement entered here will be executed in database generation, so make sure it's in correct syntax.
    Create statement of stored procedure
    Create statement of stored procedure

Moving or duplicating a procedure to another procedure container

  1. Select the procedure to move or duplicate.
    Select procedures to move or duplicate
    Select procedures to move or duplicate
  2. Drag over the target procedure container.
    Drag procedure towards the target procedures container
    Drag procedure towards the target procedures container
  3. If you want to duplicate the procedures, press on the Ctrl key and release the mouse button. If you want to move them from source to target procedure container, just release the mouse button.
    Procedures are moved
    Procedures are moved

Creating stored procedure resultset

  1. Select Stored Procedure ResultSet from diagram toolbar.
    Select Stored Procedure ResultSet
    Select Stored Procedure ResultSet
  2. Click on the diagram to create a stored procedure resultset shape.
  3. Enter the name of the resultset.
    Resultset created
    Resultset created

Assigning stored procedure resultset to stored procedure

  1. Right click on the stored procedure and select Open Specification... from the popup menu.
  2. In the Procedure Specification window, specify the Return resultset.
    Assigning stored procedure resultset to stored procedure
    Assigning stored procedure resultset to stored procedure

Related Resources

The following resources may help you to learn more about the topic discussed in this page.

 
8. Modeling Database View Table of Contents 10. Modeling Triggers in ERD
 

Technical Support

Have technical issues or suggestions? Please contact Visual Paradigm Support Team.

Sales Support

Have questions related to registration, licensing or payment? Feel free to contact Visual Paradigm Sales Team.

Discussion Forum

Share your suggestions of opinions at VP Discussion Forum.