You can translate the question and the replies:

Developing VQL Procedures

I'm trying to develop some VQL procedures and need help to figure out how to configure the input-parameters optional and/or nullable. Can't find any good descriptions on how to do this. Please assist to resolve this issue. Thanks
user
18-04-2023 03:17:05 -0400
code

1 Answer

Greetings, For user-created VQL stored procedures, input only parameters (IN) are mandatory, and a value/argument needs to be passed whenever the procedure is invoked. However, you could use IF conditions in the procedure definition to bypass one or more of the input parameters. Here is an example: ``` CREATE OR REPLACE VQL PROCEDURE procedure_max_value (valuea IN INTEGER, valueb IN INTEGER, valuec IN VARCHAR, valmax OUT INTEGER) AS ( a INTEGER; b INTEGER; c INTEGER; abcmax INTEGER; ) BEGIN a:=valuea; b:=valueb; IF valuec = '' THEN //a predefined value could be set to eliminate the variable from comparison c:= '-1'; c:= CAST (c as integer); IF a > b THEN abcmax:=a; ELSE abcmax:=b; END IF; ELSE c:=CAST (valuec as integer); IF a > b THEN IF a > c THEN abcmax:=a; ELSE abcmax:=c; END IF; ELSE IF b > c THEN abcmax:=b; ELSE abcmax:=c; END IF; END IF; END IF; RETURN ROW (valmax) VALUES (abcmax); END; ``` Moreover, you could make use of Denodo’s list of predefined [VQL procedures](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/stored_procedures/predefined_stored_procedures/predefined_stored_procedures) ,where depending on their definition and purpose, input parameters are defined optional and can be dismissed whenever the procedure is invoked(see[example](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/stored_procedures/use_of_stored_procedures/use_of_stored_procedures#:~:text=GET_VIEWS)). In addition, java procedures can be used which allow the specification of nullable input parameters. Check the relevant specification [here](https://community.denodo.com/docs/html/browse/8.0/en/vdp/javadoc/com/denodo/vdb/engine/storedprocedure/StoredProcedureParameter.html#StoredProcedureParameter-java.lang.String-int-int-boolean-) and the [documentation](https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures) on developing such procedures. Examples are provided on <DENODO_HOME>/samples/vdp/storedProcedures. Hope this helps!
Denodo Team
19-04-2023 08:06:23 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here