You can translate the question and the replies:

Returning a Select Statement in a VQL Stored Procedure

Hi everyone. Simple question here. I want to create a VQL Stored Procedure that returns a Select Statement, how do I do it? I have searched through the Denodo documentation for this but haven't found any reference about it. Thanks in advance
VQL Stored procedure VQL stored procedures Denodo 8.0
user
19-04-2024 12:01:32 -0400
code

3 Answers

Hi, To create a VQL stored procedure that returns a SELECT statement, you can use the Design Studio wizard. Navigate through the following menu: **File** > **New** > **Stored procedure** > **VQL stored procedure**. You will need to define the input and output parameters for your procedure using the **`CREATE VQL PROCEDURE`** statement. This is carried out within the **`BEGIN`** and **`END`** blocks. Please note that to use this feature, you need either the *Denodo Enterprise* or *Denodo Enterprise Plus* bundle. For more information, refer to the [Denodo Platform - Subscription Bundles](https://community.denodo.com/docs/html/browse/8.0/en/platform/installation/appendix/denodo_platform_feature_packs/denodo_platform_feature_packs#denodo-platform-subscription-bundles). Additionally, you may find the [Stored Procedures](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/stored_procedures/stored_procedures) guide useful for developing various types of stored procedures in Virtual DataPort. Hope this helps!
Denodo Team
 Edited on: 22-04-2024 18:47:35 -0400
code
Thanks for the reply. But what I was trying to figure out is how to print a result using a VQL Stored Procedure. For example: EXECUTE 'SELECT * FROM (...)'. And then I need to get this result after running the Stored Procedure.
user
22-04-2024 06:40:32 -0400
Hi, I would look into using **cursors** to process a SELECT statement and store the results. This involves the following steps: * Start by defining a cursor-type variable in the declaration area using the syntax `CURSOR <cursorName:literal> IS '<SELECT statement>'`. * After the cursor has been defined, if you want to fetch the results of the SELECT statement, you have to open the cursor with `OPEN <cursorName>`. If your cursor is created with a parameterized SELECT query, specify the value for the parameterized value. For example:`OPEN <cursorName> PARAMETERS (param1) VALUES (<literal>)`. * Once the cursor is open, you can begin fetching the results with the command `FETCH <cursorName> INTO variables/list of variables`. This command allows you to fetch row values by inserting the values of various columns into a variable list. * Finally, when you're done using the cursor, close it using the `CLOSE <cursorName>` command. For more details and examples on using cursors, please refer to **Example 2** in the [Developing VQL Stored Procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_vql_stored_procedures) guide. Hope this helps!
Denodo Team
26-04-2024 17:34:42 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here