Use of Stored Procedures in Creating Views

Virtual DataPort stored procedures can be used to create new derived views.

More specifically, an invocation of a stored procedure may appear in the FROM clause of any query or view definition. The stored procedure will be considered a view whose schema includes all input and output parameters of the stored procedure.

To create a view that combines data obtained by a stored procedure, open the dialog to create the view, click the tab “Model” and drag the procedure from the Server Explorer to the dialog of the new view (join, union, minus, intersect, etc.). Then, if the procedure includes input parameters, they can be added to the new view by clearing the Use Input Parameters check box (see Parameters of a procedure added to a new view). Otherwise, the user has to fill in the input values.

Parameters of a procedure added to a new view

Parameters of a procedure added to a new view

Note

The “Stored procedure parameters” dialog is not currently available on the Design Studio but future updates will extend the current options to progressively add them. Meanwhile, the parameters should be specified in the value section as shown in the figure below. See section Conditions with Compound Values of the VQL Guide for more details about this syntax.

Parameters of a procedure added to a new view using Design Studio

Parameters of a procedure added to a new view using Design Studio.