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
- Select Stored Procedures from diagram toolbar.
Select Stored Procedures - Click on the diagram to create a stored procedures shape. Note again that this is a container of stored procedures, not the procedure itself.
- Enter its name.
Stored procedures created - To create a stored procedure, right click on the stored procedures shape and select New Procedure from the popup menu.
- Enter the physical name of the procedure .
Stored procedure added - Right click on the procedure and select Open Specification... from the popup menu.
- 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
- Select the procedure to move or duplicate.
Select procedures to move or duplicate - Drag over the target procedure container.
Drag procedure towards the target procedures container - 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
- Select Stored Procedure ResultSet from diagram toolbar.
Select Stored Procedure ResultSet - Click on the diagram to create a stored procedure resultset shape.
- Enter the name of the resultset.
Resultset created
Assigning stored procedure resultset to stored procedure
- Right click on the stored procedure and select Open Specification... from the popup menu.
- In the Procedure Specification window, specify the Return resultset.
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 |
