1. Home
  2. Docs
  3. Chapter 14. Database Design & Engineering
  4. 2. Database Designer’s Guide
  5. Modeling stored procedures in ERD

Modeling stored procedures in ERD

Download PDF

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 in an ERD, 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
  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
  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
  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

Moving or duplicating a procedure to another procedure container

  1. Select the procedure to move or duplicate.
    Select procedures to move or duplicate
  2. Drag over the target procedure 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

Creating stored procedure resultset

  1. Select Stored Procedure ResultSet from diagram toolbar.
    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

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