Create Dynamic Procedure on Data Virtuality Returning Table Row Count

In this tutorial, I will share codes of a Procedure on Data Virtuality virtual schema that returns the number of rows of a database table or view which is passed to the procedure as a string variable. If you are working on Data Virtuality logical datawarehouse solution, you already know to create virtual schemas and procedures on those virtual schemas. Now we will creae a procedure which executes dynamic SQL on target data source.

For the sake of this tutorial, on my local DataVirtuality installation, I have defined the data source named SQLServer2019 connecting to a SQL Server 2019 instance. And I created a virtual schema named SQLServerVS as seen in following screenshot.

Data Virtuality Data Explorer window

If SQL developers double clicks on Procedures node under the related Data source node, SQLServer2019 in this case, they will see only one procedure named native which invokes translator with a native query that returns results in array of values. In fact, this native procedure executes the given SQL statement on the data source enabling code push-down to the underlying data source.
I will show how to execute native procedure for SQL programmers in following sections in this tutorial.

Open a new SQL editor (Alt+E) and copy following SQL code and execute the SQL command which creates a new procedure "TableRowCount" on virtual schema "SQLServerVS"

create procedure on Data Virtuality virtual schema

If I need to describe the steps of the procedure, it does following tasks:
Procedure takes only one string variable which is the name of the table which exists on the target data source
Procedure returns an integer parameter which is the number of the records in table given as input parameter
A dynamic SQL statement is created simply to execute "SELECT Count(*) FROM table_name"
Using native procedure on target data source, the SQL statement created dynamically is executed on that SQL Server instance. The procedures are executed using CALL statement on Data Virtuality.
Data which is returned from native procedure as output is seperated into its columns using ArrayTable function. The columns list matches to the returning list in this sample.


CREATE VIRTUAL PROCEDURE SQLServerVS.TableRowCount (

 table_view_name string

)

returns (

 cnt integer

)

as

begin



declare string sqlstr;

sqlstr = 'SELECT count(*) as cnt FROM ' || table_view_name || ';';



select d.*

from

(

 call "SQLServer2019.native"(

  "request" => sqlstr

 )

) s,

ARRAYTABLE(

 s.tuple

 COLUMNS



 cnt integer

) d;



end;;

Code

After you execute the above CREATE Procedure command on Data Virtuality SQL Editor, after you refresh the virtual schema, you will see a new procedure is created on that virtual schema.

Data Virtuality virtual schema objects

Now double click on procedure name "TableRowCount"
A new SQL editor screen will be opened and following code will appear. You might see the same code at the end of an existing SQL Editor screen too. Just copy the SQL code and paste it in an empty editor screen.

execute procedure using CALL command on Data Virtuality

Replace the string_table_view_name value with a database table or view name which exists on underlying data source. In our case on SQL Server 2019 database instance.
For example, I have created tables named Employees, Customers, etc.

Now execute your CALL Procedure command which should be similar to following code blocks


call "SQLServerVS.TableRowCount"(

 "table_view_name" => 'invoice'

);;



-- or

call "SQLServerVS.TableRowCount"('invoice');;

Code

Both CALL procedure commands will return the same value which represents the number of rows in that database table or view name