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
- Select Stored Procedures from diagram toolbar.
- 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.
- 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 .
- 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.
Moving or duplicating a procedure to another procedure container
- Select the procedure to move or duplicate.
- Drag over the target procedure 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.
Creating stored procedure resultset
- Select Stored Procedure ResultSet from diagram toolbar.
- Click on the diagram to create a stored procedure resultset shape.
- Enter the name of the resultset.
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.
