Published at November 8, 2019by Jörg Brandeis

With SAP HANA 2.0 SPS04, another feature has been added to the SQLScript language: embedded functions. These allow the use of imperative SQLScript code within a SELECT query. Such functions are created for exactly this one query and are executed only there. Since these functions are not given a name, they are also referred to as anonymous functions.

In the following example, the query with an embedded function is called within a procedure.

• The embedded function starts on line 11 and ends on line 37. It contains imperative coding, which makes the entire procedure imperative.
• The parameter IV_MAX of the procedure is passed on line 11 to the parameter IV_A of the function. This is then used on line 24 as the upper limit for the FOR loop. Direct access to IV_MAX within the function is not possible.
• The WHERE clause on line 38 once again illustrates that this is a query.
• With the CALL statement on line 41 you can test the procedure.

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 generally advise against using embedded functions, since even simple functions considerably reduce the readability of the code. You could clearly see this in the example shown as well.

As an alternative, you can create a separate UDF function or split the query into two steps:

• Create a table variable with the imperative code
• Query against this table variable

This decomposition makes the code easier to read. And it allows you to inspect the intermediate results in the debugger. However, sometimes these alternatives are not possible for technical reasons. In such cases, embedded functions allow us to use imperative code directly within a query.

SAP documentation on embedded functions