Stored procedure returning data only with "call" statement and not with "select"

I have created a stored procedure which returns an Array. It is not performing any DML operations and is only retrieving data, parsing it and giving back an array. However, I can only view the data using "call" statement. The "select" statement is returning no data. "call" and "select" should return the same array output for a stored procedure. Is it possible to have no data returned from "select" in any use case ?
user
06-04-2017 17:07:00 -0400

5 Answers

Hi, You could execute a stored procedure in Denodo Platform using CALL or Select statement, For example, I would use any one of the following ways to execute the stored procedure “CATALOG_METADATA_VIEWS” with two input parameters “database_name” and “view_name”: * With CALL statement: ``` CALL CATALOG_METADATA_VIEWS('<DATABASENAME>','<VIEWNAME>') ``` * With Select statement: ``` Select * from CATALOG_METADATA_VIEWS('<DATABASENAME>','<VIEWNAME>') ``` * With Select statement and input parameter in Where Clause: ``` Select * from CATALOG_METADATA_VIEWS() where database_name = '<DATABASENAME>' and view_name = '<VIEWNAME>' ``` You could also refer the section “STORED PROCEDURES” in[ Advanced VQL guide](https://community.denodo.com/docs/download/document/Denodo%20Express/Denodo%206.0/Advanced%20VQL%20Guide) for more details on the execution methods. Hope this helps.
Denodo Team
10-04-2017 08:34:15 -0400
Hi, We already have this information from Advanced VQL guide and also the Denodo Developer Guide. I am trying to flatten the array output from a stored procedure so that I can use the output of the stored prcedure in a query. I can see the output of the procedure if I use "call <Stored-Procedure Name>" as an array with data. But if I use the stored procedure in a query, an array is returned with "null" Data.
user
10-04-2017 16:19:13 -0400
Well, I somehow fixed this issue. It had to do something with the number of elements in the struct of an array. I reduced them and it began working. Now even if I increase the number of elements, it still works. Not sure of the issue. But this probably might fix it.
user
10-04-2017 16:21:23 -0400
We have the same requirement to use the output (an array) from a stored procedure in a VQL statement. Can you share how you got this working? We cannot find the right syntax to do this in VQL. We can call the procedure from the VQL shell using the CALL statement.
user
20-11-2017 08:48:48 -0500
Hi, When I execute a stored procedure with SELECT statement, I would ensure to pass the mandatory parameter values. If the issue persists and if you are a valid support user, you could raise a support ticket so that the Denodo support team can help you. Hope this helps !!
Denodo Team
26-12-2017 07:24:00 -0500
You must sign in to add an answer. If you do not have an account, you can register here