Calling Stored Procedures in VDM Using Advanced Query

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Demonstrate how to execute a Stored Procedure from within VDM using the Advanced Query interface, including how to pass parameters and structure the call.

What Are Stored Procedures?

Stored Procedures are predefined SQL scripts that are stored in a database and executed by name. Instead of rewriting complex queries repeatedly, users can call a stored procedure that performs the desired operations.

Tip: Stored procedures support parameters, allowing for dynamic filtering and reusability in different Views and scenarios.

Why Use Stored Procedures in VDM?

• Simplifies querying

• Reuses existing database logic

• Reduces SQL duplication in Views

• Allows for parameter-driven execution

• Useful for pre-approved or complex logic stored in your database

Use Case: You want to use an established report procedure created by your database team, but don’t want to retype the entire SQL logic in VDM.

Steps to Call a Stored Procedure in VDM

Step 1: Open VDM and Go to Advanced Query

• Launch VDM

• Open or create a View

• Click on Advanced Query from the toolbar

Step 2: Write the Stored Procedure Call

In the Advanced Query editor, use the following format:

{CALL rmb_view.AccountRepScorecard_ByUser(?StartDate?, ?EndDate?, ?UserID?)}

• Replace StoredProcedureName with the actual name of your procedure

• Replace the ?ParameterName? values with your view’s parameters

Tip: Parameters must be defined in the View’s Parameters list to be recognized in the Advanced Query.

Step 3: Run the Stored Procedure

• Click Run View

• VDM will execute the stored procedure and return the result set

Step 4: Save the View

• Once verified, save your View to preserve the stored procedure setup

• You can reopen the View later to reuse the same stored procedure logic without re-entering the SQL.

Article Summary

Stored procedures allow you to execute reusable SQL logic directly from VDM using the Advanced Query editor. You can pass dynamic parameters and simplify complex queries by referencing existing logic stored in the database. This makes it easier to maintain Views and collaborate with database teams.

Use Case: Execute monthly sales reports or scorecards with different user inputs (dates, user ID) without modifying SQL—just call the stored procedure and pass parameters from the View.