You can translate the question and the replies:

Recursive Stored Procedure

Hello, I'm implementing several stored procedures that call each other and themselves. However, when I use "select" or "call" within the stored procedure, I get a syntax error. Does anyone know how to call a stored procedure inside another one? Thank you in advance, Ismail
user
25-07-2023 09:44:44 -0400
code

1 Answer

Hi, Here's some sample code for a recursive VQL Stored Procedure. The SP name is SP_RECURSION, it only concatenates the number 1-10 in a single string: ``` (n_in IN INTEGER, concat_out OUT VARCHAR) AS ( CURSOR my_cursor IS 'SELECT concat_out from test_database.sp_recursion() where n_in = :my_param'; curs_r my_cursor%ROWTYPE; concat_s VARCHAR; n_var INTEGER; ) BEGIN IF n_in >= 10 THEN concat_s := n_in || '-'; RETURN ROW(concat_out) VALUES (concat_s); ELSE n_var := n_in + 1; OPEN my_cursor PARAMETERS (my_param) VALUES(n_var); LOOP FETCH my_cursor INTO curs_r; concat_s := curs_r.concat_out; concat_s := concat_s || n_in || '-'; RETURN ROW (concat_out) VALUES (concat_s); EXIT WHEN my_cursor%NOTFOUND; END LOOP; CLOSE my_cursor; END IF; END ``` However you should also consider [Developing Java Stored Procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures) that are much more flexible and can be easily imported in Virtual DataPort. Hope this helps
Denodo Team
26-07-2023 08:50:43 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here