You can translate the question and the replies:

How to execute a dynamic sql or a stored procedure for a view?

Hi, I used to create a stored procedure in cisco information server(CIS) to produce dynamic sql query. For example, few input params for query columns. However, I am told that I need to wrtie Java code for stored procedure in Denodo. Is it ture? I am just wondering if there is an alternative soltuion for it.
user
27-06-2018 03:23:14 -0400
code

3 Answers

Hi It seems that you want to execute SQL created dynamically by a stored procedure in Cisco Information Server (CIS). I’d try create a base view from a query, which allows executing code returned by the stored procedure by following the documentation for [Creating Base Views from a Query to a Stored Procedure](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/jdbc_sources#creating-base-views-from-a-query-to-a-stored-procedure). This will allow you to execute these stored procedures through Denodo. If the output of your stored procedure is dynamic, you may need to create a custom stored procedure or a custom wrapper to manage it. This development process is described [here](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/stored_procedures/stored_procedures). I hope this helps!
Denodo Team
27-06-2018 09:33:21 -0400
code
HI Denodo Team Actually, I am migrating the data virtualization layer from CIS to Denodo. The CIS stored produce is below. How to do it in Denodo? PROCEDURE getProduct( in in_dimension_name_1 varchar(50) -- dynamic dimension , out result_set /PROCS/SP.productCursor ) BEGIN -- Sample declare v_sql longvarchar; set v_sql = case when in_dimension_name_1 = 'dim1' .... --build dynamic sql when in_dimension_name_1 = 'dim2' .... --build dynamic sql ; open result_set for v_sql; END
user
27-06-2018 21:36:08 -0400
Hi It seems like your proposed stored procedure has a [CASE WHEN](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/case_clause_examples/case_clause_examples) syntax, which Denodo VQL supports as long as you don’t need to use PL/SQL in the statement. Based on the code of your most recent comment, it seems that you are checking the value of one variable only. If the results have the same schema in all cases of your dynamic sql, you could try create a Partitioned Union (defining that value **in_dimension_name_1** in the WHERE condition). At runtime, Denodo optimizer will use a branch pruning optimization, and only one of the branches will be executed depending on the value passed to the query. The partioned Union is documented [here](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/removing_redundant_branches_of_queries_partitioned_unions). I hope this helps!
Denodo Team
05-07-2018 13:02:27 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here