With SAP HANA 2.0 SPS04, another feature has been added to the SQLScript language: The embedded functions. These enable the use of imperative SQLScript code within a SELECT query. These functions are created and executed only for exactly this one query. This reminds you of an anonymous block, which is why they are also called Anonymous functions.
In the following example, the query is called with an embedded function within a procedure.
- The embedded function starts at line 11 and ends at line 37. It contains imperative coding, making the whole procedure imperative.
- The parameter IV_MAX of the procedure is passed to the parameter IV_A of the function in line 11. This parameter is then used in line 24 as the upper limit for the FOR loop. Direct access to IV_MAX within the function is not possible.
- The WHERE condition in line 38 shows once again that this is a query.
- You can test the procedure with the CALL statement in line 41.
I do not recommend the use of embedded functions, as even simple functions can considerably impair the readability of the code. You could clearly see this in the example shown.
As an alternative, a separate UDF function can be created or the query can be split into two steps:
- Creation of a table variable with the imperative code
- Querying this table variable
This decomposition makes it easier to read the code and it is possible to view the intermediate results in the debugger. However, if these alternatives are not possible for technical reasons, the embedded functions allow us to use imperative code directly in a query.
This post is also available in: Deutsch