Anonymous Functions in SQLScript

Veröffentlicht am 8. November 2019 von

Jörg Brandeis

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.

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.

SAP Documentation for embedded functions in SQL queries