Creating views on SQL Server Functions and Stored Procedures

Goal

This document shows how to execute SQL Server functions and stored procedures creating base views for them.

Content

Virtual DataPort allows connecting to databases using JDBC data sources. Once the connection to the database is set base views can be created in VDP. These base views can be created in two ways:

• Importing a database table, function or stored procedure directly from the graphical interface using introspection.
• Creating a view with the “Create from query” option using a SQL query on the data source.

When using the first option, VDP displays an introspection tree for the data source that shows the schemas contained in the data source with its schemas and the tables, functions and stored procedures defined for each one.

The ability to create base views by importing functions or stored procedures directly for SQL Server was added to the Denodo Platform from version 5.5 update 20150629. In the previous version of the Denodo Platform the second option of using a SQL query must be used.

When using the second option, the SQL query will be sent to the database without modifications other than interpolation variables. These interpolation variables use the syntax @VARIABLE or @{VARIABLE}.

With this option, any SQL query can be performed in the data source, including the execution of SQL functions and stored procedures. The syntax that can be used to create a base view can depend on the underlying database server. For instance, to execute a function or stored procedure from Microsoft SQL Server the following syntax must be used:

• For SQL functions:

• SELECT <schema>.<package>.<function_name>(<param1>, <param2>)

• For Stored Procedures:

• EXEC <schema>.<package>.<proc_name> <param1>, <param2>

• This kind of stored procedures (only input fields) returns the output in an internal stored procedure ResultSet.

• CALL <proc_name> (<param1>, <param2>)
• CALL <proc_name> (?, ?)

where:

• <schema>:

• Optional. Schema where the function or stored procedure has been created.

• <package>:

• Optional. Package where the function or stored procedure has been created.

• <function/proc_name>:

• Required. Name of the function or stored procedure to be executed.

• <param1>, <param2>:

• Syntax used in stored procedures with only input parameters.
• Input parameters for the function or stored procedure.
• Can use interpolation variables @INPUT_VAR1.

• The created base view will have a new field called “INPUT_VAR1” which is required to query the view.

• ?, ?:

• Syntax used in stored procedures with only output parameters.
• Output parameters for a stored procedure.
• Once the stored procedure is executed at creation time, every “?” will add a new field corresponding with an output parameter of the stored procedure in the created base view.

• The name for each “?” field in the base view will be the one defined in the stored procedure signature.

Known limitations for SQL Server stored procedures:

• Server side cursors are not supported as output parameters.
• If a Stored Procedure with output fields uses also a ResultSet with an output, VDP will be able to import it and to create a base view with all the fields including the ones for the ResultSet and the ones for the output fields defined in the procedure signature.

• When executing this kind of Stored Procedures, only the information coming from the ResultSet will be in the view results, displaying null values for the output fields defined in the signature.