You can translate the question and the replies:

Calling a Stored Procedure within another Stored Procedure

I want to know if I can call a Stored Procedure from another Stored Procedure. For example I want to Generate the Statistics for all Derived Views in a particular VDP database. The "GENERATE_STATS" stored procedure uses the View name as a parameter The "CATALOG_VIEWS" stored procedure uses multiple parameters and I am using "1" for the 8th paramter to denote Derived views only Using the Select clause, I tried to invoke the following, but it failed: select * from GENERATE_STATS(select * from CATALOG_VIEWS(NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL,NULL, NULL)); Using the CALL clause, also failed: call GENERATE_STATS(CATALOG_VIEWS(NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL,NULL, NULL)); Please let me know if this can be done using one Stored Procedure within another. Thanks.
user
23-05-2017 18:55:12 -0400
code

1 Answer

Hi,   If you want to call a stored procedure by passing values from another stored procedure, then you can follow the below steps: 1. Create a view over Denodo stored procedure, CATALOG_VIEWS in the VQL shell, example: CREATE OR REPLACE VIEW <view_1> AS SELECT * from CATALOG_VIEWS(NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL,NULL, NULL); 2. Create another view over the other stored procedure, example: CREATE OR REPLACE VIEW <view_2> AS SELECT * from GENERATE_STATS(); 3. Create a nested join between the views created in step 1 & 2 by joining the attribute “viewname” from <view_2> and “resultname” attribute from <view_1> 4. Finally, execute the nested join view to generate statistics for all the derived views available in a particular VDP database.   Also I have found another solution to achieve the same result by using Denodo Scheduler [CALL GENERATE_STATS for multiple views](https://community.denodo.com/answers/question/details?questionId=90670000000CcisAAC&title=CALL+GENERATE_STATS+for+multiple+views+%3F)   I hope this helps you!
Denodo Team
25-05-2017 04:43:19 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here