Stored procedures and macros allows to reuse written Odysseu Script.
The #PROCEDURE allows to create stored procedures that are saved into the data dictionary and can be reused by any other scripts, see #EXECUTE how to run them.
First, the name, which must be after the #PROCEDURE in the same line, is defined. After that, there might be an ordered list of variables. The order is important! These variables will be used as the parameters. Finally, there can be any Odysseus-Script code including queries etc. between the BEGIN and END. The only exception are "global" variables. Between BEGIN and END, only variables are allowed that are defined before (after the procedure name). You can execute the procedure by using the #EXECUTE command.
It is not possible to install more than one procedure with the same name, so you may delete a procedure by calling #DROPPROCEDURE before.
The example creates a procedure with name "setSomething" and has two parameters (varX and attribute). Remeber, order is important! The procedure calls a #RUNQUERY-Command including both parameters. See command how this example is used.
The #EXECUTE command can be used for running installed procedures, which were created by using the #PROCEDURE command.
The usage is similar to function calls in programming languages like Java. After #EXECUTE the name of procedure with a comma-separated list of its parameters. The parameters must be according to the definition of the procedure. If there were two parameters defined by #PROCEDURE, here are also two parameters needed. Notice, the order is important!
This example concludes the one from #PROCEDURE. Therefore we have two parameters (varX and attribute). The following executes the "setSomething" procedure and sets varX=1 and attribute=b.
The variables are replaced and the according Odysseus Script of the procedure is executed at this point. Therefore, the following is inserted insted of the #EXECUTE command:
Since this snippet is simply inserted, all things of the surrounding Odysseus Script is taken. In our example, this is for example, the choosen #PARSER and #TRANSCFG that are necessary for #RUNQUERY here. Therefore, it is not guaranteed that a procedure is executable of its own.
The command can be used to remove stored procedures, which were created by using the #PROCEDURE (see for more information about procedures) command.
This example concludes the one from #PROCEDURE. Therefore, we want to remove the "setSomething" procedure: