Anonymous Functions in SQLScript
8. November 2019
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_MAXof the procedure is passed to the parameter
IV_Aof 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_MAXwithin the function is not possible.
WHEREcondition in line 38 shows once again that this is a query.
- You can test the procedure with the
CALLstatement in line 41.
CREATE PROCEDURE test_anonymous_function (IN iv_max INT, OUT ot_result TABLE(number INTEGER, letter VARCHAR ) ) AS BEGIN ot_result = SELECT number, letter FROM SQL FUNCTION (IN iv_a INT => :iv_max) RETURNS TABLE (number INT, letter VARCHAR(1)) BEGIN DECLARE lv_cnt INT; DECLARE lv_chars VARCHAR(30) DEFAULT 'ABCDEFGHIJKLMNOP'; lt_result = SELECT 0 AS number, ' ' AS letter FROM dummy WHERE dummy <> 'X'; FOR lv_cnt IN 1..:iv_a DO lt_result = SELECT * FROM :lt_result UNION SELECT lv_cnt AS number, SUBSTRING(:lv_chars, :lv_cnt, 1) AS letter FROM dummy; END FOR; RETURN SELECT * FROM :lt_result; END WHERE MOD(number, 2) = 0 ; END; CALL test_anonymous_function(13, ?);
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.